Full Version: Lookup Tables (list/combo controls)?
UtterAccess Forums > Microsoft® Access > Access Forms
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?
In the query which is the record source for your report, include the lookup table and add the "Name" field from it into the query, instead of, or in addition to, the "item number". Then use that for the control source of the control on your report instead of the item number.

In addition, you should change the names of these fields. It is ALWAYS a bad idea to use characters like the octothorpe(#), etc, in object names. They often have often uses in Access and using them in object names tends to foster problems.

"Name" is a reserved word in Access and should never be used in its generic form as the name of a field or control. WHile you can get away with it, it's not a sound practice.

Besides, "name" is so generic as to be virutally meaningless. Other options are easily available, "ItemName" for example, or "ProductName".


Edited by: GroverParkGeorge on Wed Aug 15 11:56:08 EDT 2007.
When you say "add' the lookup to the query you want me to join on the item number and display the item name?
understand about the field naming. The actual field names are different and longer. I was using 'name' and '#' by way of example just to ask my question.
Yes, that's what you have to do to show the item name as part of the recordset returned by the query.
nclude the lookup table in the query. If you've correctly defined the relationship between the lookup table and the order table, Access will go ahead and handle the join for you. Then it's a simple matter of dragging the item name down into the query grid so it's returned as part of the recordset.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.