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.

add_dropdown_list_08

Setup dropdown list (Combo Box) using Lookup Wizard

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

add_dropdown_list

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

add_dropdown_list_01

Select “Table: Personal_data”

add_dropdown_list_02

Select “Employee ID”

add_dropdown_list_03

Select sorting order

add_dropdown_list_06

Adjust field width

add_dropdown_list_07

Click Finish

add_dropdown_list_04

Now go back to the Design View of Job_data Table

add_dropdown_list_05

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.

add_dropdown_list_09

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.

add_dropdown_list_10

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

 

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *