MS Access —

Modifying a Lookup List

You can display and modify the properties of a lookup field by clicking the Lookup tab in the Field Properties section.
The lets you modify what appears in a lookup list

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:

  • To sort the records in a lookup list. For example, to sort the records in a lookup list alphabetically by last name.

  • To add, change, or delete the static options in a value list. For example, you could add "U.S. Postal Service" to a Ship Via value list.

In this lesson you will learn how to view and modify an existing lookup field.

  1. 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.

  2. 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. You can learn more about these properties in Table.

  3. 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 field you can use the familiar query grid to create the SQL statement for you.

  4. Click the Row Source button to display the SQL Statement: Query Builder window.

    The SQL Statement: Query Builder window appears, as shown in figure. Yepit's the same query grid that you're already familiar with.

  5. In the LastName field, click the Sort box list arrow and select Ascending, as shown in figure.

    This will sort the CustomerID lookup field by the LastName field.

  6. 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.

  7. Click the Ship Via field.

    The Row Source box contains the value list options.

  8. 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 (;).

  9. 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.

  10. Save your changes, close the tblCustomerTours table and the database.

Lookup Field Properties
Property Description

Display Control

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.

Row Source

Determines what is displayed in the lookup field. The Row Source property setting depends on the Row Source Type property setting.

Bound Column

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.

Column Count

The number of columns that are displayed in the lookup field list.

Column Widths

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.

TO MODIFY A LOOKUP LIST:

  1. DISPLAY THE TABLE IN DESIGN VIEW.

  2. CLICK THE LOOKUP LIST'S FIELD NAME BOX, THEN CLICK THE LOOKUP TAB IN THE FIELD PROPERTIES SECTION.

  3. CLICK THE ROW SOURCE BUTTON TO DISPLAY THE SQL STATEMENT: QUERY BUILDER WINDOW.

  4. MAKE THE DESIRED CHANGES AND THEN CLOSE THE SQL STATEMENT: QUERY BUILDER WINDOW.