Display field in Detail section

[PhilPPhillips]

I have a form that lists all of the inventory items our service guys used. Each item is assigned a code indicating who the service guy was that used that part. The service guy code corresponds to another table that stores their code, full name and other data. My question is, how do I display the service guys name on each detail line? If I use an unbound field, all of the records show the same service guy's name. If (on the query used to populate the form) I link the table with the names and display it as a bound item and a user changes the service guy code the display name doesn't change, obviously. But if I add an event to update the name after the service guy code is changed, it updates the linked table and changes the name of the original service guy to the new service guy.

Does that make any sense?

Basically, I need to know how you can display a name on a detail line based on a lookup of another table.

 

[spizotfl]

look at help for dlookup, that might do what you need.

"Maturity is a bitter disappointment for which no remedy exists, unless laughter can be said to remedy anything."
-Vonnegut

 

[PhilPPhillips]

If I put the dlookup command as the control source for a unbound textbox will it work on a detail line?

I've been trying it out and so far all I can get is the same answer for all the detail lines.

 

[spizotfl]

is this a subform in datasheet or continuous?

"Maturity is a bitter disappointment for which no remedy exists, unless laughter can be said to remedy anything."
-Vonnegut

 

[PhilPPhillips]

It is a continuous form.

Basically the form lists (in the detail section) all of the items used by the service guys. Each record is assigned a loc (location) code. I would like to have a field show the location's name from the table where the locations are stored.

If I use this as the control source I get 89's loc_name for every detail item (as I would expect)

=DLookUp("[loc_name]","dbo_imlocfil_sql","[loc] = '89'")

But if I try this i get #name? for all detail items:

=DLookUp("[loc_name]","dbo_imlocfil_sql","[loc] = " & [loc1])