We have seen in the last section of this lesson that you can use a query to retrieve lookup field values for you. However, Access gives you the ability to specify the values that can be used in a ‘lookup field’ yourself.

In this example, we will help prevent improper data from being entered into the Number of Cylinders field of the Vehicles table. We will create a value list that will let a user pick how many cylinders a car has from a list of options. To create this value list, select the Vehicles table and enter Design view. In the Data Type field of Number of Cylinders, select Lookup Wizard. The Lookup Wizard window will appear.

In the Northwind sample database, a query was used to find the corresponding product names for each ProductID. However, we want to make a list of options to choose from, so select the second radio button, and click Next:

 

 

The next page of the Wizard is where you enter the values you want to use for the value list.

 

 

In this page of the Wizard we can specify the number of columns for the value list and which values we want to include in the list. (The majority of lookup fields/value lists you will use will only be a single field at a time.) Click your mouse inside the first cell, type a value, and press Tab on your keyboard to move to the next cell. When you have entered the list of values you want to use, click Next.

The final step of the Wizard asks you to name the lookup column (value list). The default name is the same name as the field, but you can name it whatever you like. Click Finish to complete the Wizard. If you open Datasheet view for the table you will be able to use the combo box to fill in a value for the field.