Rain
Step away from the keyboard...
The lookup datatype may seem attractive, but it causes considerable
confusion. The general consensus (see tablesdbdesign newsgroup) is that it
causes more confusion than it solves. This is partly because what is
displayed is NOT what is stored in the field in the table.
Besides, Access tables store data and Access forms display it ... if you are
working directly in the tables, now is a good time to start brushing up on
forms. To handle the lookup in a less confusing way, create a form
(preferably based on a query against the table). In that form, add a
combobox based on the lookup table (the table that holds the
potentially-looked-up values). Bind this combobox control to the field in
your main table that holds ... the ID of the looked up value.
This way, the looked up value can be displayed (on the form), but the
underlying ID is being stored as a foreign key. For the record, it is this
foreign key/underlying ID that is being stored when you use the lookup
datatype, but there are no clues.
Good luck!
Regards
Jeff Boyce
Microsoft Office/Access MVP
