Add dropdown list in Access Table Field

This Access tutorial explains how to add dropdown list in Access Table Field using List Box and Combo Box.

Add dropdown list (Combo Box) in Access Table Field

Suppose you have two tables.

Personal_data – store personal information of employees

Personal_data

Employee_ID	Employee_Name
001	Sam
002	Apple
003	May

Job_data – store the position title of an employees

Job_data

Employee_ID	Position
001	HR Manager

For an HR database, a personal record is created before Job record is created. Therefore we can add a dropdown list for Employee_ID in Job_data Table, using the Employee_ID from Personal_data.

Setup dropdown list (Combo Box) using Lookup Wizard

Right click on Job_data Table > Design View > in Data Type, select Lookup Wizard

Select “I want the lookup field to get the values from another table or query”

Select “Table: Personal_data”

Select “Employee ID”

Select sorting order

Adjust field width

Click Finish

Now go back to the Design View of Job_data Table

In the Lookup tab of Employee_ID field, the Display Control has changed to Combo Box, and Row Source becomes a SQL statement. (Row Source is what you can select in Combo Box)

If you don’t know SQL, click on the … button on the right of Row Source, then you will go to Query Builder interface.

If the value is duplicated in the source, add DISTINCT keyword in SQL after keyword SELECT, or GROUP BY the required field in Query Builder.

Example – Add dropdown list (List) in Access Table Field

We just learned how to use another Table Field value as a Row Source, we can also manually define the list value. Let’s say we want to use to select from a list of Position.

Go to Design View of Job_data Table, set up the Lookup tab of Position Field as below.

Allow Value List Edits is optional, but it can help you quickly edit list items in Data View.

You can do it with Combo Box or List Box, as both Properties in Lookup tab are extremely similar (both allow multiple selection).

However, I would suggest to use List Box for custom values, because you can edit the Row Source easily using the Edit List Items windows, Combo Box does not have it.

Go to Data View of Job_data Table, you can now select a position.

Outbound References

https://support.office.com/en-us/article/Create-a-list-of-choices-by-using-a-list-box-or-combo-box-70ABF4A9-0439-4885-9099-B9FA83517603

 

 

Add dropdown list in Access Table Field