You need to first create another table which contains the values you wantto
list, each as a separate row in the table. If the values to be looked up are
unique, then the 'referenced' table and the 'referencing' table can have the
same column, e.g. a States table could have a State column, and the
referencing table, e.g. a table of Cities, can also have a State column.
These are known as 'natural' keys. Often, however, the values won't be
unique, e.g. a Cities table could have the same city name multiple times
because city names can legitimately be duplicated. So in this case the
Cities table would have a CityID column, and a City column and a State column.
In this case the CityID is a 'surrogate' key, most probably an autonumber, to
give each row a unique identifier. A referencing table would then alsohave
a numeric CityID column, but not an autonumber this time.
Taking cities as an example, a combo box on a form based on a referencing
table, e.g. of addresses, would be set up like this:
Name: cboCity
ControlSource: CityID
RowSource: SELECT CityID, City, State FROM Employees ORDER BY City,
State;
BoundColumn: 1
ColumnCount: 3
ColumnWidths: 0cm;3cm;3cm
ListWidth: 6cm
If your units of measurement are imperial rather than metric Access will
automatically convert them. The important thing is that the first
ColumnWidths dimension is zero to hide the first column. Experiment with the
other two to get the best fit. The ListWidth is the sum of the ColumnWidths.
In this example, having selected a city from the list you'll se its name in
the combo box. To see the state for the selected city you can add an unbound
text box to the form with a ControlSource of:
=cboCity.Column(2)
The Column property is zero-based, so Column(2) is the third column, i.e.the
state. Note that this means you don't need, and shouldn't have, a State
column in the table of addresses. That would be redundancy and leave the
table at risk of inconsistent data. Storing just the CityID automatically
tells you the state via the relationships.
When you create a referencing (aka 'lookup') table like this you should
create a relationship between it and the referenced table (on CityID in this
case) and enforce referential integrity. This ensures that (a) only valid
values can be entered in the referencing table, and (b) a row cannot be
deleted from the referenced table while a matching row still exists in the
referencing table. The integrity of the data is thus protected.
Finally, a word of warning. The 'lookup wizard' you see listed in the data
types of a field in table design view will build this sort of thing for you.
Don't use it! For reasons why see:
http://www.mvps.org/access/lookupfields.htm
Ken Sheridan
Stafford, England