Use a values list field when you have a limited set of values that do not change often, such as rating scales or expense categories.
Note Access has other types of list fields: a lookup field that looks up matching data in a table (such as an ID field that looks up a Full Name), and a multivalued field that can store up to 100 values in one field, separated by a comma (,). For more information, see Create or delete a lookup field and Create or delete a multivalued field.
In this article
Create a values list field
-
Open the table in Design View.
-
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.
Note The Lookup Wizard creates three types of lists depending on the choices you make in the wizard: a lookup field, a values list field, and a multivalued field.
-
Carefully follow these steps in the wizard:
-
On the first page, select I will type in the values that I want, and then click Next.
-
On the second page, keep 1 column selected, enter several values, one in each row under the column header, and then click Next.
-
On the third page, under Do you want to limit entries to the choices?, select Limit to List, and then click Finish.
-
-
Save your changes.
Update the properties of a values list field
When you use the Lookup Wizard to create a values list field, the Lookup field properties are set for you. To change the design of the values list field, set it's Lookup properties.
-
Open a table in Design View.
-
Click the lookup field's name in the Field Name column.
-
Under Field Properties, click the Lookup tab.
-
Set the Display Control property to Combo Box to see all available properties changes to reflect your choice. For more information, see Lookup field properties.
-
You can edit the value list directly in the Row Source property.
Delete a value list field
Important When you delete a value list field that contains data, you lose that data permanently — you cannot undo the deletion. For that reason, you should back up your database before you delete any table fields or other database components.
Delete from Datasheet view
-
Open the table in Datasheet View.
-
Locate the value list field, right-click the header row , and then click Delete Field.
-
Click Yes to confirm the deletion.
Delete from Design view
-
Open the table in Design View.
-
Click the row selector next to the value list field, and then press DELETE, or right-click the row selector and then click Delete Rows.
-
Click Yes to confirm the deletion.
Lookup field properties
|
Set this property |
To |
|
Display Control |
Set this property to control what properties are displayed:
|
|
Row Source Type |
Choose whether to fill the lookup field with values from another table or query, or from a list of values that you specify. You can also choose to fill 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 a table or query name or to a 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 separated by semicolons. |
|
Bound Column |
Specify the column in the row source that supplies the value stored by the lookup field. This value can range from 1 to the number of columns in the row source. The column that supplies the value to store does not have to be the same column as the display column. |
|
Column Count |
Specify the number of columns in the row source that can be displayed in the lookup field. To select which columns to display, you provide a column width in the Column Widths property. |
|
Column Heads |
Specify whether to display column headings. |
|
Column Widths |
Enter the column width for each column. The display value in a lookup field is the column or columns that are represented in the Column Widths property as having a non-zero width. If you don't want to display a column, such as an ID column, specify 0 for the width. |
|
List Rows |
Specify the number of rows that appear when you display the lookup field. |
|
List Width |
Specify the width of the control that appears when you display the lookup field. |
|
Limit To List |
Choose whether you can enter a value that isn't in the list. |
|
Allow Multiple Values |
Indicates whether the lookup field allows multiple values to be selected. You cannot change the value this property from Yes to No. |
|
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 |
Name an existing form to use to 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. |
