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
| Employee_ID | Employee_Name |
|---|---|
| 001 | Sam |
| 002 | Apple |
| 003 | May |
Job_data – store the position title of an employees
| 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