Lookup Tables (list/combo boxes)?

[Nestor01]

I use a lookup table which contacts 2 fields (item#, name)

When a customer places an order, the order form contains a drop down to select the item by name (from the lookup table). The detail is stored by item # in a customer order table. The form displays the name, but is column bound to the item#.

When I want to create a report or form showing what the customer has ordered, I would like to display the name in a textbox control, but the only way I can get it to convert from the item# (stored in the field) to the 'name' is to use a list/combo control and set the lookup table. This does two things: 1) makes the field larger by having the pull-down arrows as part of the field; 2) always highlights it on the form/report. As this field is invariably disabled, it looks like crap.

Question: How can I translate the 'name' of an item in a lookup table from the item # to display the 'name' without using a combo/list control type?

[barry07]

Sounds like you need to use Dlookup. If txtitem is the item no field (which doesn't have to be visible - it can be on the form with Visible=False). then the "name" field would contain this formula:


=Dlookup("name","master_table","item=" & txtitem)

(I'm assuming item is numeric, othewise enclose it in single quotes.

Hope this helps