How to add a Lookup Control to a Form in Access 2016
Add a combo box that looks up data from another table, then displays meaningful text, rather than a number.
Whenever you create a form that contains foreign key data, you will need to decide which value to display to the user — the foreign key value (usually a number or code), or a (typically) more readable value that's stored in the primary table.
If you look at the following example, you can see that the form has a
Country Code field. This displays the country code rather than the country name.
If you wanted to display the country name to the user, you could do this by adding a lookup control. In this case, we will add a combo box that looks up the country name from the primary table.
Open the Form in Layout View
In the left Navigation Pane, right-click on the form and select
This can also be done in Design View if you prefer.
Select the Combo Box Option
Click the Combo Box button in the Ribbon (from the
Position the Combo Box
Hover your cursor over the form where you'd like to place the combo box.
A red line will be displayed where your cursor is.
Once the red line is in the correct position, click in that position.
This will launch the Combo Box Wizard.
Select the Data Source for the Control
I want the combo box to get the values from another table or query.
Select the Source Table/Query
Select the table or query that contains the value you'd like to display in the combo box.
Select the Source Field/s
Select the field/s that contain the value you'd like to be included in the combo box.
This should include any value you'd like to be displayed as well as the foreign key data.
Specify the Sort Order
Specify how you'd like the values to be sorted. This determines how the user sees the values in the combo box.
Adjust Column Width
Adjust the column width if required. You can scroll down to make sure the combo box is wide enough for all data.
If you want the foreign key data to be displayed in the combo box, uncheck
Hide key column (recommended). However, only do this if you think it will help the user.
Select an Action for the Control
Store that value in this field and select the field from the drop-down list.
This step refers to the foreign key value.
Name the Label
Enter a name for the control's label.
The label is what the user will see, so provide a meaningful name.
The lookup control has now been added to the form.
Delete the Old Field
You can delete the old field if necessary. Here's how.
Select the old control and its label (by holding the
Shift key while selecting them).
Right-click and select
Delete from the contextual menu.
Test the Form
It's a good idea to switch to Form View to test the new form control.
Click the Form View icon in the bottom right of the screen and then click on the new combo box.
The lookup values will appear in the combo box instead of the foreign key values.
If your combo box still displays the foreign key data, try saving the form, or closing and re-opening it.