Using Lookup Fields in Tables

Access 97's lookup feature for table fields lets you substitute combo (drop- down list) boxes or list boxes for conventional field text boxes. The lookup feature lets you provide a list of acceptable values for a particular field. When you select the value from the list, the value automatically is entered 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 is provided. 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 in a combo box the CompanyName field value from the Customers table. The EmployeeID field displays the LastName and FirstName fields of the Employees table, separated by a comma and space (see Figure 10.15). The foreign key lookup feature is implemented by a simple Access SQL select query, SELECT DISTINCTROW [CustomerID],[CompanyName] FROM [Customers] ORDER BY [CompanyName];, in the case of the CustomerID field.

• With any field except a single primary-key field, a list of fixed values from which to select is provided.

You can add a new lookup field in either Design view or Table Datasheet view; however, you only can add the lookup feature to an existing field in Design view. 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 Personnel Actions table you created in earlier chapters of this book is a candidate for a lookup field that uses a foreign-key drop-down list. Follow these steps to use the Lookup Wizard to change the paID field of the Personnel Actions table to a lookup field:

1.	In the Database window, select the Personnel Actions table, and press Ctrl+C to copy the table to the Clipboard.
2.	Press Ctrl+V to display the Paste Table As dialog. Enter a name for the copy, such as tblLookup, and click the OK button to create the copy.
3.	Open the table copy in Design view and select the paInitiatedBy 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 10.16). The first dialog of the Lookup Wizard appears.
4.	You want the field to look up values in another table (Employees), so accept the first (default) option (see Figure 10.17). Click the Next button to display the Lookup Wizard's second dialog.
5.	With the View Tables option enabled, select the Employees base table to which the paInitiatedBy field is related (see Figure 10.18). Click the Next button to display the third dialog.
6.	Click the > button three times to add the EmployeeID, LastName, and FirstName fields to your lookup list (see Figure 10.19). You must include the base table key field that is related to your foreign key field. Click the Next button for the fourth dialog. Figure 10.16. Selecting the Lookup Wizard to add the lookup feature to a field. Figure 10.17. Selecting between a foreign-key and fixed-list lookup in the first dialog of the Lookup Wizard. Figure 10.18. Choosing the base table or query of a foreign-key lookup field. Figure 10.19. Selecting the fields to include in your lookup list.
7.	Adjust the widths of the columns to display the first and last names without excessive trailing "white space." The wizard determines that EmployeeID is the key column and recommends hiding the key column (see Figure 10.20). Click Next to display the fifth and final dialog. Figure 10.20. Adjusting column widths of the lookup list and hiding the key column.
8.	Accept the default "label" for the lookup field in the text box (see Figure 10.21). (If you change the default value, you change the field name, not the caption.)
9.	Click the Finish button to complete the wizard's work. Click OK when the wizard asks whether you'd like to save the table design. Your new lookup field properties appear as shown in Figure 10.22. The Access SQL statement created by the wizard is SELECT DISTINCTROW [Employees].[EmployeeID], [Employees].[LastName], [Employees].[FirstName] FROM [Employees];.
10.	Click the Table View button to display the table datasheet. Only the first visible column of the list appears in the Initiated By column. Adjust the width of the Initiated By column to the width of the drop-down list, about 1.5 inches. With the caret in the Initiated By column, open the drop-down list to display the wizard's work (see Figure 10.23). Figure 10.21. Specifying the caption for the lookup field. Figure 10.22. Lookup properties added to the paInitiatedBy field. Figure 10.23. The drop-down lookup list created by the Lookup Wizard.
11.	Return to Design view, select the Row Source property of the paInitiatedBy field, and click the Build button to display the Row Source SQL statement in Query Design view (see Figure 10.24), then close the Query Design window.

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 paType field of your copy of the Personnel Actions table, follow these steps:

1.	Select the paType 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.	Enter H, Hired; Q, Quarterly Review; Y, Yearly Review; and S, Salary Adjustment in the Col1 and Col2 columns of four rows. Adjust the width of the columns to suit the entries (see Figure 10.25). Click the Next button to display the wizard's third dialog.
5.	The paType field uses single-character abbreviations for the type of personnel actions, so select Col1 as the "field that uniquely identifies the row," as shown in Figure 10.26. (The paType field does not 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. Figure 10.25. Adding the lookup list values in the Lookup Wizard's second dialog. Figure 10.26. Selecting the column that contains the value to insert into the field.
6.	Accept the default "label" for your column and click the Finish button. The lookup properties for the paType field appear as shown in Figure 10.27. The Row Source Type is Value List. The Row Source contains the following values: "H"; "Hired"; "Q"; "Quarterly Review"; "Y"; "Yearly Review"; "S"; "Salary Adjustment".
7.	Click the Table View button and save the changes to your table. Increase the width of the Type column to about 1.5 inches, place the caret in the Type column, and open the fixed value list to check the wizard's work (see Figure 10.28).
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.
9.	If you want to remove the lookup feature from a field, select the field, click the Lookup tab, and choose Text Box from the drop-down Display Control list.