lookup field

[NigelShaw]

Hi,

my only other issue!!! i have a lookup field on a table and when i view it in datasheet, i can see the actual lookup value i need however i have tried the following options:
create a form from the table
added a combo box to an existing form and gone through the wizard

both options show the id number and NOT the value i need to show. what can i do to show my actual information?

many thanks,

NS

 

[pbaldy]

Personally I would drop the lookup field. Numerous reasons here:

http://www.mvps.org/access/lookupfields.htm

 

[NigelShaw]

Hi Pbaldy,

thanks for the reply. what would you suggest? the lookup is quite important

many thanks,

NS

 

[pbaldy]

See your other thread. We all use these types of lookup tables, you just don't want that lookup field. Typically you store the key value in other tables, and get the related information with a query that joins the tables together.

 

[NigelShaw]

so, to make sure i understand....

table 1 holds the info i want to lookup
form 1 has the combo that will do the lookup
table 2 is updated by the combo
create a query to look up table 1 and have the info displayed by the combo on the form.

is that right?

sorry for my dumbness

NS

 

[pbaldy]

Not knowing something yet isn't being dumb. If it is, we're all dumb because there's always more we don't know than what we do know.

I think you have it about right, except the query would be for other forms/reports. Let's say I've got a customer table and a sales table. On the form I enter sales on, I'll have a combo that gets the customer ID and name from the customer table (the row source), but is bound to the sales table (the control source). I may or may not be displaying the ID to the user. That means that in the sales table, I'm just going to see the customer ID. When I want a report on my sales, I'm going to join the 2 tables together in a query, which allows me to display the names associated with the ID.

Clear as mud now?

 

[aasoftwaresolution]

Lookup Field

Check the combo box property

In row source give query lik

select customer_name, customer_id from customer table

in property bound column give 2

now in form it will show the customer name but store value customer_id in the table when you save .

If i mis-understand accept my appology

 

[tl mike]

With all that is said about the bads of lookup fields I have a quick question:

If have want to import from Excel and there is no lookup field how would Access know that the State Name is the same as the State ID when I do the import to the main table.

 

[boblarson]

With a lookup field and you import the import should fail anyway if you are importing to the main table and the field is set to a datatype of Long Integer which is what the ID field is even with a lookup field. With a lookup field it only displays the value associated with the id number - it doesn't store it in the table.

So, what you need to do is to import to a temp table (clear out the table before importing, not delete the table, with a delete query - DELETE * FROM YourTempTable) and then use an Append query that has both your main table and your lookup table (list of states) in it and store the id of the state in the state column.