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:
# City
1 New York
2 Melbourne
3 Paris
...
Form: So you create a form called addresses_form. In Form Properties you set the Data Tab’s Content to addresses (the table you are editing).
List Box: And you add to this form a list box control called something like City ID.
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 Sql.
-
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.
