Primary table: Say you have a table called
addresses and in it you have a field called
City ID to select a city from a list of cities.
# Address, City ID, Postal_code_ID, Country_Code_ID
1 333 Main st, 2, 33, 44 (Melbourne)
2 444 Broadway, 1, 345, 69 (New York)
Lookup table: The list of cities has two fields and looks roughly like this:
1 New York
Form: So you create a form called
Form Properties you set the
addresses (the table you are editing).
List Box: And you add to this form a list box control called something like
Then for this list box, in your
Properties: List Box dialog:
Data field should be something like
City ID, where City ID is the name of the foreign key (integer) that you are editing with this list box.
Type of list contents should be
List content should be SQL code that returns at the very least 2 fields, where the first field is what displays in the list box, and the 2nd field is the key that gets stored into your table (normally this is an integer). NOTE! this is backwards. Not ID, Text, but rather Text, ID. (I think it’s too bad this was designed this way, but we’re stuck with it this way now.)
Bound field should be
1 in this case (zero based, saying that when you select an item from the list box, the value stuffed into the table is in field 1 (in this case the integer that represents a given city name).
You can also set
Type of list contents to
Query and define a query to return the 2+ list box fields, but I don’t often do it that way unless the query will be used by more than one list box.