Automatic output from two linked tables

[Guest]

I am compiling a form for personnel records. I want to show the department
and the code associated with the employee. The department code has been
output from the employee table and I have formatted it as a drop down list.
I would like to have the department description, which is located in another
table. I have created a relationship between the two tables, employee and
chartofaccounts. I was able to create a combo box with a drop down list
which can be used for choice of description but would like to have an
automatic input to the employee form so that the person using this form would
not need to search the drop down list. Is this possible and how??

 

[Guest]

There are two places in your form module you will need to add code.
First, in the Current event so it will get the department description each
time the current record changes. You will, however, have to be sure you are
not on a new record, because at that time, the department code in your form
will be Null.

If Not Me.NewRec Then
Me.txtDeptDescription = DLookup("[Description]", "DeptTable",
"[DeptCode] = '" & Me.DeptCode & "'")
End If

Then, you will want to do the same thing in the After Update event of your
form control where the Department code is so it will handle new records and
changes to current records.

Note, all the names above are made up. Substitue the correct names for your
controls, tables, and fields.