Referential Integity with Lookup Tables

[BG]

I have a field in a table that has a combo box lookup to another table. The
combo box works for looking up legitimate values for the field. But, if I
link the lookup table to the main table using the foreign key, I cannot
enforce referential integrity. Shouldn't I be able to do this? I think the
problem may have to do with the value that is stored in the main table for a
combo box field. I believe it is the row indicator of the lookup table
values, and not the primary key value, thus the primary and foreign keys do
not match.

Any thoughts and advice is appreciated.

 

[John Vinson]

I have a field in a table that has a combo box lookup to another table. The
combo box works for looking up legitimate values for the field. But, if I
link the lookup table to the main table using the foreign key, I cannot
enforce referential integrity. Shouldn't I be able to do this? I think the
problem may have to do with the value that is stored in the main table for a
combo box field. I believe it is the row indicator of the lookup table
values, and not the primary key value, thus the primary and foreign keys do
not match.

Any thoughts and advice is appreciated.

This is one of many reasons that the vast majority of developers
dislike the Lookup field.

Your table contains a numeric ID, and it's linked to the lookup table
with referential integrity enforced ALREADY. The lookup wizard
considerately conceals both these very basic facts from your view. It
will create a relationship (and its associated indexes) even if such a
relationship ALREADY EXISTS. You're not getting a relationship
established because you're trying to link to a value which does not
exist in your table (the looked-up text).

 

[BG]

Thanks for the information. How can I see what the key to the lookup table
is? If I want to retreive all records with a certain text value, I really
have to select all records with a certain number? How can I see the real
table values?

In addition, what methodology is typically used in place of Combo box Lookup
fields?

 

[John Vinson]

Thanks for the information. How can I see what the key to the lookup table
is? If I want to retreive all records with a certain text value, I really
have to select all records with a certain number? How can I see the real
table values?

You shouldn't ever really need to *see* the numeric key, except for
debugging purposes. To do so, open the table in design view; select
the field; choose the Lookup tab on the field properties (at the
bottom left); and change the lookup type from Combo Box to Textbox.

In addition, what methodology is typically used in place of Combo box Lookup
fields?

By all means use a combo box to look up values! It's the correct
technique and works fine. But use it *ON A FORM*, a form based on your
table - not in the table datasheet. The table datasheet itself should
almost never be on display, particularly to users; they should
interact with the data through Forms, using combo boxes and the other
tools provided for the purpose. Similarly, they never really need to
see the value of Autonumber keys.

 

[BG]

Thanks a bunch for the education. I guess I was doing some things right,
just could not explain the link to lookup tables based on what I could see.