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.

  1. Screenshot of Access with the Layout View option highlighted

    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.

  2. Screenshot of Access with the Combo Box option highlighted

    Select the Combo Box Option

    Click the Combo Box button in the Ribbon (from the Design tab).

  3. Screenshot of positioning the combo box

    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.

  4. Screenshot of 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.

  5. Screenshot of the Combo Box Wizard

    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.

  6. Screenshot of the Combo Box Wizard

    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.

  7. Screenshot of the Combo Box Wizard

    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.

  8. Screenshot of the Combo Box Wizard

    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.

  9. Screenshot of the Combo Box Wizard

    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.

  10. Screenshot of the Combo Box Wizard

    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.