Using Lookup Fields in Tables

Access 2003's lookup feature for table fields lets you substitute drop-down list boxes or list boxes for conventional field text boxes. The lookup feature is a one-to-many query that Access automatically creates for you. The lookup feature lets you provide a list of acceptable values for a particular field. When you select the value from the list, the lookup feature automatically enters the value in the field of the current record. You can specify either of the following two types of lookup field:

• In a field that contains foreign-key values, a list of values from one or more fields of a related base table. The purpose of this type of lookup field is to add or alter foreign-key values, preserving relational integrity by assuring that foreign-key values match a primary-key value. A relationship must exist in the Relationships window between the tables to define a field as containing a foreign key.

  As an example, the Orders table of Northwind.mdb has two foreign-key fields: CustomerID and EmployeeID. The lookup feature of the CustomerID field displays the CompanyName field value from the Customers table in a drop-down list. The EmployeeID field displays the LastName and FirstName fields of the Employees table, separated by a comma and space (see Figure 11.19).

Figure 11.19. A query against the Employees table generates the lookup list of the Orders table's EmployeeID field.

• In any field except a single primary-key field, a list of fixed values from which to select. Field lists are equivalent to validation rules that specify allowable field values, so a fixed lookup list isn't appropriate in this case.

You can add a new lookup field in either Table Design or Table Datasheet view; however, in Design view you can add the lookup feature only to an existing field. In Datasheet view, only the combo box control is displayed, even if you specify a list box control. You can display a combo box or a list box on a form that is bound to a table with lookup fields. In practice, the drop-down list (a combo box with the Limit to List property set to Yes) is the most common type of lookup field control. The following sections describe how to add foreign-key and fixed-list lookup features to table fields.

Adding a Foreign-Key Drop-down List with the Lookup Wizard

The HRActions table you created in earlier chapters of this book is a candidate for a lookup field that uses a foreign-key drop-down list. If you didn't create and populate the HRActions table, you'll find it in the \Seua10\Chaptr06\Nwind06.mdb database on the accompanying CD-ROM. Import the HRActions table into Northwind.mdb.

Tip

Before using the imported HRActions table, open it in Design view, select the InitiatedBy field, and delete the General Number value from the Format property. If you don't remove the Format property, lookup fields with text values are right-justified, which is inconsistent with the justification of other text fields.

Follow these steps to use the Lookup Wizard to change two fields of the HRActions table to lookup fields:

1. In the Database window, select the HRActions table and press Ctrl+C to copy the table to the Clipboard.

2. Press Ctrl+V to display the Paste Table As dialog. Type a name for the copy, such as tblHRLookup, and click the OK button to create the copy with the structure and data.

3. Open the table copy in Design view and select the InitiatedBy field. Click the Lookup tab to display the current lookup properties; a text box control has no lookup properties. Open the Data Type drop-down list and select Lookup Wizard (see Figure 11.20) to open the first dialog of the Lookup Wizard.

   Figure 11.20. You start the Lookup Wizard from the Data Type field of the Table Design grid, despite the fact that Lookup Wizard isn't a Jet data type.

   

4. You want the field to look up values in another table (Employees), so accept the first (default) option (see Figure 11.21). Click Next to open the Lookup Wizard's second dialog.

   Figure 11.21. The first Lookup Wizard dialog has options for the two types of lookup fields.

   

5. With the View Tables option enabled, select the Employees base table to which the InitiatedBy field is related (see Figure 11.22). Click Next to display the third dialog.

   Figure 11.22. The second Wizard dialog asks you to select the table to provide data for the lookup columns.

   

6. Click the > button three times to add the EmployeeID, LastName, and FirstName fields to your lookup list (see Figure 11.23). You must include the base table primary-key field that's related to your foreign-key field. Click Next for the fourth dialog.

   Figure 11.23. The third dialog requests you to specify the fields to include in the lookup list. You must include the table's primary-key field.

   

7. 

   The fourth dialog lets you sort the list by up to four fields. In this case, you don't need to apply a sort order, so click Next to open the fifth dialog.

8. Adjust the widths of the columns to display the first and last names without excessive trailing whitespace. The Wizard determines that EmployeeID is the key column and recommends hiding the key column by marking the check box (see Figure 11.24). Accept the recommendation, and click Next to display the fifth and final dialog.

   Figure 11.24. Verify the fields to appear in the lookup list, and adjust the column widths to suit the data.

   

9. Accept the default InitiatedBy as the label for the lookup field in the text box of the final Wizard dialog. The label you specify doesn't overwrite an existing Caption property value. Click Finish to complete the Wizard's work.

10. Click Yes when the message asks whether you want to save the table design. Your new lookup field properties appear as shown in Figure 11.25. The simple Jet SQL query statement created by the Wizard as the Row Source property is SELECT [Employees].[EmployeeID], [Employees].[LastName], [Employees].[FirstName] FROM [Employees];.

    Figure 11.25. The Lookup page of the InitiatedBy field displays the lookup list property values added by the Wizard.

    

    Tip

    Preceding step 6 adds fields in their table order, but you can add fields with the Lookup Wizard in any order you prefer. Alternatively, you can rearrange columns by editing the Row Source property's SQL statement after you create the lookup list.

11. Click the View button. Only the first visible column of the list appears in the Initiated By column. With the cursor in the Initiated By column, open the drop-down list to display the Wizard's work (see Figure 11.26).

    Figure 11.26. The lookup list of the InitiatedBy field has LastName and FirstName columns. Some FirstName values are truncated, because the column width setting didn't compensate for the width of the vertical scroll bar. The last names in the InitiatedBy field are right-justified if you don't remove the Format property value from the field.

    

12. To change the SQL statement to open a single-column, alphabetized LastName, FirstName list, return to Design view, select the Row Source property of the InitiatedBy field in the Lookup page, and press Shift+F2 to open the Zoom dialog. Edit the SQL statement as follows:

     SELECT Employees.EmployeeID,   Employees.LastName & ", " & Employees.FirstName FROM Employees ORDER BY LastName, FirstName; 

    Click OK to close the Zoom dialog.

13. Change the value of the Column Count property to 2 and the Column Widths property to 0";1.3". Optionally, change the List Rows value to 9 to accommodate Northwind's nine employees without a vertical scroll bar. Click View, and then click Yes to save your changes, and open the lookup list to verify your changes (see Figure 11.27).

    Figure 11.27. A single-column lookup list, like that used for the EmployeeID of the Orders table, is better suited to selecting peoples' names.

    

Tip

Make sure to correct the lookup field's name to the original value if the Lookup Wizard changes it. The Wizard changes the field name if it isn't the same as the base table's field name. Although Name AutoCorrect can handle field name changes, it's a much better database design practice to freeze the names of tables and fields. Change table and field names during the development process only if absolutely necessary.

If you need a list of the properties of the combo box control created by the Wizard, see "Adding Combo and List Boxes" p. 596.

Adding a Fixed-Value Lookup List to a Table

You add the alternative lookup feature a fixed list of values using the Lookup Wizard in much the same way as you created the foreign-key lookup list in the preceding section. To add a fixed-list lookup feature to the ActionType field of your copy of the HRActions table, follow these steps:

1. In Design view, select the ActionType field, open the Data Type list, and select Lookup Wizard to launch the Wizard.

2. In the first Lookup Wizard dialog, select the I Will Type in the Values That I Want option and click the Next button.

3. In the second Lookup Wizard dialog, type 2 in the Number of Columns text box and press the Tab key to create the second list column.

4. Type H, Hired; Q, Quarterly Review; Y, Yearly Review; S, Salary Adj.; R, Hourly Rate Adj.; B, Bonus Adj.; C, Commission Adj.; T, Terminated in the Col1 and Col2 columns of eight rows. (Don't include the commas or semicolons.) Adjust the width of the columns to suit the entries (see Figure 11.28). Click the Next button to display the Wizard's third dialog.

   Figure 11.28. Specify the number of columns and type values in the second Wizard dialog for a lookup value list.

   

5. The ActionType field uses single-character abbreviations for the type of HRActions, so select Col1 as the "field that uniquely identifies the row." (The ActionType field doesn't uniquely identify the row; Col1 contains the single-character value that you want to insert into the field.) Click the Next button to display the fourth and final Wizard dialog.

6. Accept ActionType as the label for your column and click the Finish button. The lookup properties for the ActionType field appear as shown in Figure 11.29. The Row Source Type is Value List. The Row Source contains the following values:

    "H";"Hired";"Q";"Quarterly Review";"Y";"Yearly Review"; "S";"Salary Adj.";"R";"Hourly Rate Adj.";"B"; "Bonus Adj.";"C";"Commission Adj.";"T";"Terminated" 

   Figure 11.29. Compare the Lookup properties page for a lookup value list with that for a lookup list based on a related table (refer to Figure 11.25).

   

7. Click the View button and save the changes to your table. Place the cursor in the Type column, and open the fixed value list to check the Wizard's work (see Figure 11.30).

   Figure 11.30. Datasheet view displays the fixed-value lookup list for the ActionType field.

   

8. If you don't want the abbreviation to appear in the drop-down list, change the first entry of the Column Widths property value to 0.

Tip

To remove the lookup feature from a field, select the field, click the Lookup tab, and choose Text Box from the Display Control drop-down list.

Note

The lookup feature has generated controversy among seasoned database developers. Relational database purists object to embedding queries as table properties. Another objection to the use of foreign-key, drop-down lists is that it is easy for uninitiated users to inadvertently change data in a table after opening the list. Access 2003's lookup feature, however, is a useful tool, especially for new database users.