Modifying an existing lookup field isn't nearly as straightforward as creating one. You can display and modify the properties for a lookup field by clicking on the Lookup tab in the Field Properties section. There are several reasons why you would want to modify a lookup field:
In this lesson you will learn how to view and modify an existing lookup field.
Switch to Design view by clicking the View button on the toolbar.
The CustomerID lookup field we created in an earlier lesson is cool, but what if the list of names isn't displayed in alphabetical order? Not a problemyou can change this by the lookup field. Display and change the properties for a lookup field by clicking the Lookup tab in the Field Properties section.
Click the CustomerID field, then click the Lookup tab in the Field Properties section.
The properties for the CustomerID lookup field are displayed, as shown in Figure 4-30. You can learn more about these properties in Table 4-10.
Click the Row Source box.
That technical SELECT [tblCustomers].[CustomerID] stuff in the Row Source box is a SQL statement. SQL (Structured Query Language) is a language most database programs use to create queries; it tells lookup fields where to get their values. Fortunately, you don't have to know how to write SQL to modify a lookup fieldyou can use the familiar query grid to create the SQL statement for you.
Click the Row Source button to display the SQL Statement: Query Builder window.
The SQL Statement: Query Builder window appears, as shown in Figure 4-31. Yepit's the same query grid that you're already familiar with.
In the LastNa me field, click the Sort box list arrow and select Ascending, as shown in Figure 4-31.
This will sort the CustomerID lookup field by the LastName field.
Close the SQL Statement: Query Builder window and click Yes when you are prompted to save your changes.
Access updates the SQL statement for the CustomerID lookup field. You can also view, change, or delete options from a value list using the Lookup tab.
Click the Ship Via field.
The Row Source box contains the value list options.
Click the Row Source box.
The Row Source box contains the text "Airbone";"FedEx";"UPS". You can add options to the value list by typing them into the Row Source boxjust make sure that the options are enclosed by quotation marks (") and separated by a semicolon (;).
Type ; "US Mail" so that the Row Source reads "Airbone;"FedEx";"UPS";"US Mail".
That's ityou've finished modifying the lookup fields in the tblCustomerTours table.
Save your changes, close the tblCustomerTours table and the database.
Table 4-10. Lookup Field Properties
Determines whether the lookup field is a text box, combo box, or list box.
Row Source Type
Determines how Access provides data to the lookup field: from a table or query, from a list of values specified in the Row Source box, or from a list of field names in a table or query.
Determines what is displayed in the lookup field. The Row Source property setting depends on the Row Source Type property setting.
The column in the lookup list that contains the value that is actually stored in the field. The bound column is the first column (1) by default.
The number of columns that are displayed in the lookup field list.
The width of each column that is displayed in the lookup field list. Setting a column width to 0 hides the column.
Limit to List
Determines whether a field can accept a value that is not in the lookup list.