Lookup Column Example (1 Viewer)

Hello everybody, I was wondering if anybody has or knows of an example database which uses a lookup column to automatically populate a table field.


For example, if a database has 2 tables:

Your table is incorrect, CountryName should not be a field in tblCity. Once you have the CountryID in tblCity, you use a query to obtain the CountryName from the tblCountry table.

The way to populate CountryID in tblCity is via a form. That form would be based on tblCity and would have a dropdown to choose the country. The dropdown would be based on tblCountry. It would store the CountryID field in tblCity, yet it would show the CountryName. This page details that:

https://www.techonthenet.com/access/comboboxes/bind_index.php

Lookups on tables cause insurmountable problems when you use code and queries. And, since no user ever looks at a table, there is no reason EVER to use them. Use combos on forms. If you need to see both the "ID" and the "name" in a query, include a join to the lookup table and select the "ID" from the main table and the "name" from the lookup table. That is the best solution for reports since we don't use combo boxes on reports. For forms, the combo handles the "user view" so it isn't necessary to join to the lookup field unless there are other pieces of data that you want to show on your form.

One of the basic problems with table level lookups is that they mask the actual value in the column. When you open the table in DS view, it looks like the field shows the country name but in fact, the field holds the country ID. If you use code and try to search for "United States of America", you will be sorely disappointed because you are actually searching the ID field and so need to use the ID rather than the name. And other problems are actually worse.