Unexpected results after creating a Table Lookup

[Guest]

When I use the table lookup wizard to it changes my datatype to Number and it
seems that my field properties for Required has stopped working. I want to
have a table lookup to help in data entering of route numbers, street names
and paper types. I want all of these fields to contain data but after I
created the table lookup this functionality has gone away.

 

[Guest]

It sounds like the primary keys of the tables you are looking up the values
from are number data types, most likely autonumbers. The wizard has created
a long integer foreign key field in the referencing table which references
these numeric keys. The combo boxes which you use to look up the values hide
the true numeric value and show the value from another field in the
referenced table which corresponds to the value of the numeric key column.

This will still work, and you can reset the Required property of the column
to true in table design view. If the values being looked up in the
referenced table are all unique, however, you can dispense with the
'surrogate' numeric keys and use the column containing the text (or whatever)
values as the primary keys of the referenced tables. The foreign key columns
in the referencing tables can then revert to the text (or whatever) columns
as before and you can amend the properties of the combo box so that it shows
these values (you'll need to change its RowSource, ColumnCount and
ColumnWidths properties for this).

However, its strongly advised that you don't use a 'lookup' like this in
table design itself. Leave the table to show the actual values stored in the
columns and use a combo box on a form for data entry to select values from
the referenced tables.

Ken Sheridan
Stafford, England