Modify a Lookup List

How to Modify a Lookup List in Access

Modify 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 many different reasons why you would want to modify a lookup field, including:

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

Modify a Lookup List

  1. In Design View, click the field name for a field that contains a lookup list based on a table or query.
  2. Click the Lookup tab.
  3. Click the Row Source box.

    The technical looking text in the Row Source box is an S-Q-L statement that tells the Lookup list where to get its values. Fortunately, you don’t need to know SQL to modify a Lookup list. You can use the familiar query grid to create the SQL statement for you.

  4. Click the Row Source Build button.
    Modify a Lookup List

    In the Query Builder window, you can change the fields included in the lookup list, or add or remove a sort.

  5. Make the desired changes and then click the Query Builder window’s Close button.
  6. Click Yes.
    Modify a Lookup List

Modify a Value List

  1. In Design View, click the field name for a field that contains a value list that you want to modify.
  2. Click the Lookup tab.
  3. Click the Row Source box.

    The Row Source box contains the value list options.

    You can add or edit options in the value list by typing in the Row Source box.

  4. Edit the value list options as desired in the Row Source box.

    Make sure that the options are enclosed by quotation marks and separated by a semicolon.

    Modify a Lookup List

That’s all there is to adding an option to a value list.

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 Counts The number of columns that are displayed in the lookup field list.
Column Determines whether the field names, captions, or first row of data is used as a column heading.
Column Widths The width of each column that is displayed in the lookup field list. Setting a column width to 0 hides the column.
List Rows The maximum number of rows that appear in the list box.
List Widths The width of the list box that is displayed.
Limit to List Determines whether a field can accept a value that is not in the lookup list.
Allow Multiple Values Determines whether multiple values for this lookup column are allowed.
Allow Value Field Edits Determines whether the values in the lookup column can be edited.
List Items Edit Form Lists a form to open when lookup values are being edited.
Show Only Source Values Determines whether only values that match the row source are displayed.

FREE Quick Reference

Click to Download