Filling in value of field from other table based on previous field entry

[Guest]

Howdy

I have a question about filling in a field's value. I am actually modifying the sample "Time and Billing" database in Access 2003, and I have a question about filling in values in a field based on values in another field. I have seen this done with a macro, but unfortunately, I do not know how it was done, hence my question. :

I have a form called Timecards Subform, which is based on the Timecard Hours table. In that table (and form) are two fields of interest: Work Code ID and Billing Rate. There is also a Work Code table with two fields, Work Code and Fees. The Work Code table exists for us to list all of our billable work codes and assign a fee (usually hourly) to them. So, for example, there might be an entry called "Excel training" and the Fee is $75. That table would contain all of our work codes and fees

The Timecard Hours table would be used by an employee to list the work performed for a specific client. So, right now, the employee can go to the Work Code ID field, which is a combo box, and choose the appropriate Work Code (which populates from the Work Code field of the Work Code table). However, the employee has to manually look up and enter the Billing Rate in the Timecard Hours table. What I would like is for the Billing Rate field in the Timecard Hours table (or the form, whichever) to lookup the value just entered in the Work Code ID field (which is actually a value from the Work Code field of the Work Code table) and then fill in the appropriate value from the Fees field of the Work Code table. Does that make any sense? I am trying to make it so that the employee just has to pick the right Code from the drop-down combo box, and then the Billing Rate will automatically popluate from the Fees field, so that they just enter the number of hours worked and don't have to worry about knowing the right Billing Rate

I would appreciate any help you can offer. I saw this work in a database at a previous company, but unfortunately, that company is out of business and I do not have any way to access that database. Thanks again for your help in advance

Have a blessed day

Jeff Frank

 

[tina]

first of all, i hope you are not using any Lookup fields in the Timecard
Hours *table*. in a form based on that table, of course - a combo box there
is entirely appropriate. but Lookup fields directly in tables is not a good
idea at all. for details on why, see
http://www.mvps.org/access/lookupfields.htm

next, strictly speaking, data normalization rules say that you should not
save the hourly fee in the Timecard Hours table, because it is already
stored in the Work Code table - and the two tables are linked on the Work
Code ID field.

but if you have a good business reason for saving that hourly fee from the
Work Code table into the Billing Rate field in the Timecard Hours table,
then the automation you looking for can be done.
in your Timecards Subform, the Work Code ID field should be bound to a combo
box, whose RowSource is the Work Code table. the settings should be
something like this:

RowSource: SELECT [Work Code].[Work Code], Fees FROM [Work Code] ORDER BY
[Work Code].[Work Code];
*note: in your post, it appears the Work Code table has a field in it with
the exact same name. suggest you change one name or the other, so they're
not identical.*
Column Count: 2
Column Width: 1";0"
*note: this setting means the Work Code will show up in the droplist, but
the Fee will not.*
Bound Column: 1

to automatically populate the Billing Rate field with the appropriate Fee,
add a procedure to the combo box's AfterUpdate event, as

Private Sub cboWorkRateID_AfterUpdate()

With Me
If Not IsNull(.cboWorkRateID) Then
.BillingRate = .cboWorkRateID.Column(1)
Else
.BillingRate = Null
End If
End With

End Sub
*note: cboWorkRateID.Column(1) is referring to the Fee column in the combo
box.*
substitute the correct control names in the code above, of course.

hth

Howdy,

I have a question about filling in a field's value. I am actually

modifying the sample "Time and Billing" database in Access 2003, and I have
a question about filling in values in a field based on values in another
field. I have seen this done with a macro, but unfortunately, I do not know
how it was done, hence my question.

I have a form called Timecards Subform, which is based on the Timecard

Hours table. In that table (and form) are two fields of interest: Work Code
ID and Billing Rate. There is also a Work Code table with two fields, Work
Code and Fees. The Work Code table exists for us to list all of our
billable work codes and assign a fee (usually hourly) to them. So, for
example, there might be an entry called "Excel training" and the Fee is $75.
That table would contain all of our work codes and fees.

The Timecard Hours table would be used by an employee to list the work

performed for a specific client. So, right now, the employee can go to the
Work Code ID field, which is a combo box, and choose the appropriate Work
Code (which populates from the Work Code field of the Work Code table).
However, the employee has to manually look up and enter the Billing Rate in
the Timecard Hours table. What I would like is for the Billing Rate field
in the Timecard Hours table (or the form, whichever) to lookup the value
just entered in the Work Code ID field (which is actually a value from the
Work Code field of the Work Code table) and then fill in the appropriate
value from the Fees field of the Work Code table. Does that make any sense?
I am trying to make it so that the employee just has to pick the right Code
from the drop-down combo box, and then the Billing Rate will automatically
popluate from the Fees field, so that they just enter the number of hours
worked and don't have to worry about knowing the right Billing Rate.

I would appreciate any help you can offer. I saw this work in a database

at a previous company, but unfortunately, that company is out of business
and I do not have any way to access that database. Thanks again for your
help in advance.