Query containing a field on lookup

[303factory]

Hi

I have a table with a field that is on a lookup to another table.

So the field 'ExhibitStatus' on table 'tblExhibitInfo' is on lookup to table 'tblStatus', linked by ID, where tblStatus is:
ID Status
1 Open
2 Closed
3 In progress

Etc. Now this works fine when you open up tblExhibitInfo. The different statuses appear as text in a drop down box you can select. However when I try to have this field on a query, it doesnt work.

In the query design, it's
Field: ExhibitStatus
Table: tblExhibitInfo

but when you open the query the field just contains the ID number instaed of the text, and if you click on the drop down box you get a list of blank rows to choose from.
Also if you link this query to a form you cant even see the ID, it's just blank with with a choice of blank rows on the combo box.

Any ideas where I'm going wrong?

Thanks

Dan

 

[pbaldy]

Sounds like you have the lookup at the table level, which most of us recommend against:

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

 

[303factory]

Sounds like you have the lookup at the table level, which most of us recommend against:

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

I see.. So I should have the status as a text field in the tblExhibitInfo instead of a lookup, but keep the row source of the drop down box from the tblStatus?

Let me know if there is a more elegant solution!

Thanks for your help

 

[303factory]

Actually one further question:

I've done as I mentioned above, set a combo box on my subform with the control source as 'status' (text field) and the row source a list of available statuses from a different table.

When it loads the combo box is blank instead of displaying the value from the table. IF you click it it displays the correct list of available statuses, but it doesnt let you change it (ding!).. it's propery is not set to locked so not sure why this is happening.

Is it impossible to do what I'm trying to do here?

 

[neileg]

No, what Paul was saying is that you should not have a table level lookup, you should still use a lookup table, but relate the two tables using a form, probably with a combo box. You should be storing the ID and not the text value.