Customize how data is displayed by creating a lookup field

When you want a field in a datasheet, form, or report to display a certain value while the field stores a different value, you create a lookup field. A lookup field is useful when you want the field to display something more meaningful than a number, such as an ID (or other foreign key (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) value). For example, instead of displaying a contact ID number, Microsoft Office Access can display a contact name. In this example, the contact ID number is called the bound value, or the value that is stored in the source table, query, or list. The contact ID number is automatically looked up in the source and replaced with the contact name. The contact name is referred to as the display value.

 Note   The difference between a lookup field's display value and its bound value is that the display value is displayed in Datasheet view while the bound value is the stored value.

When a lookup field is viewed or accessed in a datasheet, it is called a lookup column.

Lookup fields can be either simple or complex. Simple lookup fields let you store only a single value. Complex lookup fields or multi-value lookup fields let you store multiple values that are displayed in a combo box or a list box control. For each entry in a multi-value field, a display value is "looked up" based on its corresponding bound value. Providing a choice of lookup values helps avoid data entry errors by limiting the values that can be entered.

What do you want to do?


Create a new lookup field

You can create a lookup field that will retrieve values from a table or a query, or from a list of values that you create.

 Note   When you provide a list of values for a lookup field, the values in the field of the current record are automatically entered.

Select lookup values from a table or query

  1. In the Navigation Pane, right-click the table that you want to work with, and then click Design View.
  1. Click the cell in the Data Type column that corresponds to the column that you want to define as a lookup field, click the down arrow, and then click Lookup Wizard.
  1. In the Lookup Wizard, ensure that the I want the lookup column to look up the values in a table or query option is selected, and then click Next.
  2. To view and select the source for the lookup field value, on the next page, under View, select the Tables, Queries, or Both option, and then click Next.
  3. Under Avaliable Fields, select the field that you want to add.
  4. Click the select button select button.
  5. Repeat this step if you want to add more fields, and then click Next.
  6. To specify sort options for the lookup field, on the next page of the wizard, select the down arrow next to the first sorting field, and then click the name of the field by which you want to sort the values.
  7. To change the sort order from ascending to descending, click the Ascending button, and then click Next.
  8. To adjust the width of the lookup field, on the next page of the wizard, use the mouse to drag the column to the width that you want, and then click Next.

 Tip   Leave the Hide key column check box selected so that anyone using the lookup column sees only the values that you want them to see in the field and not the values in the primary key field.

  1. On the next page of the wizard, type a label name for the lookup column.
  2. To create a multi-value lookup column, select the Allow Multiple Values check box.

 Note   This check box must be selected to enable storing multiple values.

from the lookup wizard, selecting the multi-value lookup option

  1. Click Finish.

 Tip   The properties that apply to a specific lookup field are available on the Lookup tab, under Field Properties.

Using a table or query as the data source for a lookup field

Select lookup values from a list

  1. In the Navigation Pane, right-click the table that you want to work with, and then click Design View.
  1. Click the cell in the Data Type column that corresponds to the column that you want to define as a lookup field, click the down arrow, and then click Lookup Wizard.
  1. In the Lookup Wizard, select I will type in the values that I want, and then click Next.
  2. On the next page of the wizard, type the number of columns that you want in your lookup list.
  3. Type the values that you want in the lookup list, and then click Next.

 Note   You can adjust the width of the lookup field but if you reduce the width so that it is no longer visible, it will not appear in the lookup column.

  1. If you specified more than one column in step 4, you must choose which column you will use to uniquely identify each value. In the Available Fields box, double-click the column that you want to use to uniquely identify each value, and then click Next.

 Note   This step does not appear if you did not specify more than one column in step 4.

  1. On the next page of the wizard, type a label name for the lookup column.
  2. If you are creating a multi-value lookup column, select the Allow Multiple Values check box.

 Note   This check box must be selected to enable storing multiple values.

  1. Click Finish, and then clickYes to save your changes.

Top of Page Top of Page

Change an existing field to a lookup field

  1. In the Navigation Pane, right-click the table that you want to work with, and then click Design View.
  1. Click the cell in the Data Type column that corresponds to the column that you want to define as a lookup field, click the down arrow, and then click Lookup Wizard.
  1. Follow the instructions to complete the wizard.

Top of Page Top of Page

Remove a field's lookup property

  1. In the Navigation Pane, right-click the table that you want to work with, and then click Design View.
  1. Select the field whose lookup property you want to remove.
  2. Click the Lookup tab.
  3. In the Display Control drop-down list, click Text Box.

Top of Page Top of Page

Set or change lookup field properties

When you use the Lookup Wizard, the lookup field properties are automatically set. However, you can modify these properties to change the behavior of a lookup field.

  1. In the Navigation Pane, right-click the table that you want to work with, and then click Design View.
  1. Click the field whose lookup properties you want to change.
  2. Click the Lookup tab.
  3. Click the drop-down arrow next to the property that you want to change, and select the option that you want.

The following table lists the properties that you can set or change.

Property Action
Display Control

Depending on the control type, the type can be set to one of the following:

  • Check Box    Yes/No fields only.
  • Text Box    Shows content that has been entered by a user.
  • List Box    Shows a list of values in an open window.
  • Combo Box    Shows selected values when closed and shows the available list of values when open.

 Note   Setting the Display Control property to either Text Box or Check Box disables lookups.

Row Source Type Choose whether to populate the lookup field with values from another table or query, or from a list of values that you specify. You can also choose to populate the list with the names of the fields in a table or query.
Row Source Specify the table, query, or list of values that provides the values for the lookup field. When the Row Source Type property is set to Table/Query or Field List, this property should be set to the name of a table, query, or SQL statement that represents the query. When the Row Source Type property is set to Value List, this property should contain a list of values that are separated by semicolons.
Bound Column Specify the column in the Row Source property that supplies the value stored by the lookup field. This value can range from 1 to the number of columns in the Row Source property.
Column Count Specify the number of columns in the row source that can be displayed in the lookup column. This property can be an integer value from 1 to 255.
Column Heads Specify whether to display column headings. The value can be No (default) or Yes.
Column Widths Enter the column width for each column. For example, if you don't want to display a column, such as an ID number, you can specify 0 for the width.
List Rows Specify the number of rows that appear when you display the lookup column.
List Width Specify the width of the control that appears when you display the lookup column.
Limit To List Choose whether a user can enter a value that isn't found in the list.
Allow Multiple Values

Specify whether the lookup field uses a multivalued field and allows multiple values to be selected. This setting can be No (default) or Yes. If you select Yes, the user will be able to select multiple values from the Row Source property for each record.

For more information about multivalued fields, see the article Guide to multivalued fields.

Allow Value List Edits Specify whether you can edit the items in a lookup field that is based on a value list. When this property is set to Yes, and you right-click a lookup field that is based on a single column value list, you will see the Edit List Items menu option. If the lookup field has more than one column, this property is ignored.
List Items Edit Form Specify an existing form to use with which you can edit the list items in a lookup field that is based on a table or query.
Show Only Row Source Values Show only values that match the current row source when Allow Multiples Values is set to Yes.

To programmatically look up a value in a table, see the reference article DLookup Function.

Top of Page Top of Page

 
 
Applies to:
Access 2007