SETTING UP A FIELD'S LOOKUP PROPERTIES FOR USE ON FORMS
Before Access 95, when you wanted a lookup on a field such as a combo box, you had to create it at least once and then copy it from one form to another. Now, Access allows you to create the lookup at the table level and have it propagate when you use the field on the various forms, using any type of lookup control that you specify.
Access gives you three types of controls to choose from: TextBox (the default), ListBox, and ComboBox. Figure 10.1 shows an example of a lookup control combo box for the Rating field in the MovieTitles table, which is on the CD-ROM in the chap10.mdb database in the ExamplesChap10 folder.
Figure 10.1. Setting lookup fields at the table level can save time later in the application cycle.
Be aware of a couple of items when specifying lookup properties at the table field level:
Although it's not shown in Figure 10.1, the Ratings table specified in the Row Source property isn't located in the back end.
Tip
You can specify tables from the front end as row sources for the lookup field properties of a field in tables on the back end.
Unlike some of the other properties of a linked table, the lookup properties of a field in the linked table can be set on the front end.
Caution
When a linked table has lookup properties set in the front end and then is deleted, even when the table is relinked, the lookup properties are reset. To keep the lookup properties for a linked table, reset and refresh the Connect property.
To set the lookup properties at the table level, follow these steps:
1. |
Open the table you want to add the lookup properties for in Design view. |
2. |
Highlight the field for which you want to specify the lookup properties. |
Note
The field used for a lookup is usually a foreign key for data in another table—in this case, the Rating field, which is the foreign key for the Ratings table's primary key.
3. |
Set the Row Source Type and Row Source properties just as you do on a form. |
After you set the properties for the field at the table level, the control type you chose at the table level is reflected when you place the field on a form. Figure 10.2 shows an example of the Rating field that has its lookup properties set at the table level.
Figure 10.2. The lookup properties, when set at table level, propagate to any new forms created.
Combo boxes, next to text boxes, probably are the most-used type of control for allowing data entry. The next section covers combo box techniques in more detail.