UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Lookup Tables (list/combo controls)?    
 
   
Nestor01
post Aug 15 2007, 10:48 AM
Post#1



Posts: 13
Joined: 1-July 07



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?
Go to the top of the page
 
GroverParkGeorge
post Aug 15 2007, 10:55 AM
Post#2


UA Admin
Posts: 35,129
Joined: 20-June 02
From: Newcastle, WA


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".

HTH

George
Edited by: GroverParkGeorge on Wed Aug 15 11:56:08 EDT 2007.
Go to the top of the page
 
Nestor01
post Aug 15 2007, 11:01 AM
Post#3



Posts: 13
Joined: 1-July 07



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.
Thanks!
Go to the top of the page
 
GroverParkGeorge
post Aug 15 2007, 12:25 PM
Post#4


UA Admin
Posts: 35,129
Joined: 20-June 02
From: Newcastle, WA


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.
George
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th June 2019 - 01:49 PM