How to add a Lookup Control to a Form in Access 2016
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 Layout View.
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 Design tab).
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
Select I want the combo box to get the values from another table or query.
Click Next.
Select the Source Table/Query
Select the table or query that contains the value you'd like to display in the combo box.
Click Next.
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.
Click Next.
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.
Click Next.
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.
Click Next.
Select an Action for the Control
Select Store that value in this field and select the field from the drop-down list.
Click Next.
This step refers to the foreign key value.
Name the Label
Enter a name for the control's label.
Click Finish.
The label is what the user will see, so provide a meaningful name.
