Microsoft Access for Beginners
- Additional Information
Using Lookup Fields
Access uses lookup fields to provide a selection of possible values for a field.
This selection can be obtained from another table or from a list of values manually
entered into the field settings. A good example from the demo database is the Department
field in tblPersonnel. The table stores a number for the department which references
a department record in tblDepartments. As shown in the screenshots below, tblPersonnel
uses a lookup field to display the department name from tblDepartments although
it only stores the department number.
By using its relationship to tblDepartments, the personnel table stores a number
corresponding to a given department.
Because of the lookup function added to the Department field of tblPersonnel for
this example, the table displays the appropriate title even though only its number
is stored.
The settings for lookup fields are found in the Lookup tab under the individual
field settings of the table design screen. These settings allow for a lot of flexibility
in the way the data is displayed in the table view. Lookup settings are also inherited
by form fields created from the table field.
Display Control
This setting has four options; Text Box, Combo Box, List Box and Check Box (for
Yes / No fields only). Setting it to text box removes any lookup function from the
field. In a table view, there isn't much difference between a combo and list box.
On a form, the combo box provides a drop down list which takes up less room than
a list box as shown in the comparison below.
Row Source Type
This setting determines what type of lookup will be performed.
A Table / Query lookup is exactly that, with the data coming from either a table
or query in the database or a query added to the control. This is a good option
for the example shown above where the data is coming from tblDepartments. A field
can also reference itself when doing a lookup. This might be done if you have a
field where you want the lookup to remember values that the user has entered in
the past. Every time a new value is entered into the table, it's added to the lookup
because the field is looking up its own values.
A Value List lookup is appropriate when there are only a few items that you want
the lookup to present and it's not likely that the list will change. The WorkStatus
field in tblPersonnel is a good example of this. There is a small number of status
descriptions that can be used. Presenting them in a lookup simplifies data entry
and ensures that the descriptions are entered the same way every time without misspellings.
The Field List lookup is a way in which you can list the field names from a specific
table in the lookup.
Row Source
This setting specifies the actual source of the lookup data by referencing a table
or query, creating a new query or manually entering values. The Department field
has a very simple query behind it that pulls the Department ID from tblPersonnel
and the Department Name from tblDepartments. The link between the tables does the
rest and the department names are shown in the lookup.
In the Work Status field which uses a Value List, the values are entered manually
with a semi-colon between each one. This allows Access to recognize the separate
values. A Field List lookup will accept the name of any table or query in the database.
Bound Column / Column Count
As you can see by the Department lookup, the Row Source can have more than one column
so it's necessary to specify which of the columns supplies the value that's actually
saved to the field performing the lookup. In this case, it's the first column since
it's the Department ID that's being saved. The department name is only displayed.
The Column Count setting is especially important for a Value List lookup. Remember
that In this lookup, you supply the values manually, placing semi-colons between
them. Specifying the column count tells Access how many columns of data this actually
represents. For example, if you had a field named 'Location' and entered the following
string of values under Row Source:
"100";"Seattle";"200";"Denver";"300";"Boston";"400";"Chicago"
This could represent a list of company locations with location IDs and cities so
you would want it in two columns. Entering '2' under the column count tells the
database to show two columns, using the available values to fill in the columns
from left to right and adding rows as needed. You would see something like this.
Depending on which column you chose as the bound column, the table would then save
either the location ID on the left or the city name on the right when a user chose
a value from the lookup.
Column Heads
This option specifies if a header row should be shown in the lookup. Depending on
the source type you specify, this could be field names or, in the example above,
the first row of the Value List would be used as headers. In this case, you would
want the Row Source to read something like this:
"Location ID";"City";"100";"Seattle";"200";"Denver";"300";"Boston";"400";"Chicago"
Column Widths / List Width / List Rows
The width of the columns in the drop down or list box and the total width of the
list itself can be changed with these settings to accommodate headers and content.
The Location ID column shown above is set at 0.75".
The List Rows settings specifies the number of rows that will be displayed at one
time, not the total number of rows in the lookup. This can be useful in adjusting
the amount of space taken up by the lookup on a form or in a table view.
Limit to List
This setting determines if the user can enter new values or is limited to the choices
offered by the lookup. In the example above, you probably would not want the user
adding new locations and you would want to make sure the available locations were
entered correctly for reporting purposes so you would probably set this to 'No'.
On the other hand, if you're entering a large number of addresses, you might put
a lookup on the City field and allow the user to enter new cities as needed. This
way, the user would be presented with a list of cities already used which would
speed up data entry and cut down on error but would still be able to add new ones
as needed.
Form Lookups
Combo and list boxes can be added to forms independently of the tables although
if they are added to the table when it is designed, the lookup will carry over when
the field is added to a form design. Still, if you decide when designing the form
that a combo box is just what you need, the designer makes it easy to change between
a text box and combo box. In some cases, you might want to have a lookup field on
the form but not on the table because of the table size. The properties that the
form combo and list boxes use are also identical to the ones described in this article.
Return to Series Index