Add a lookup or values list field to an Access web app

Important    Microsoft no longer recommends creating and using Access web apps in SharePoint. As an alternative, consider using Microsoft PowerApps to build no-code business solutions for the web and mobile devices.

Use a lookup field to find ("look up") values in one table that you can use in another table. A lookup field can provide values for a dropdown list and make it easier to enter data in a field. For example, let’s say you want a list of the 50 U.S. states so your users can pick a state from the list instead of typing its name. You can use the Lookup Wizard in Access to set up a lookup field for the state name.

In this article

Create a lookup field

Create a values list field

Comparing Combo box and autocomplete controls

Create a lookup field

Before you continue, if you used a database template to create your app or added a table by using a template, Access may have already created some lookup relationships between tables. To check for lookup fields, open a table in Design view in Access and look in the Data Type column for the Lookup data type.

To use the Lookup Wizard for an Access web app:

  1. In the Access desktop program, open the table in Design view.

  2. In the first empty row in the list of fields, type a name for the new lookup field and choose Lookup in the Data Type column. This starts the Lookup Wizard.

    Important: In an Access web app, you need to add a new field and immediately set it to use the Lookup Data type. If you try to change an existing field to the Lookup Data type, you won't be able to save changes to the table's structure – even if the field has never contained any values. This limitation applies only to a web app.

  3. Click I want the lookup field to get the values from another table or query.

  4. Choose the table or query you want to use as the data source.

  5. In the Which value do you want to display in your lookup? list, choose the field or query you want to use for your lookup data.

  6. Choose how to sort the values in the Do you want to sort the items in your lookup? list.

  7. Choose an option to handle what happens when a record from the source table is deleted.

    Note: The Prevent delete if there are corresponding records… option is usually the best choice, because it helps retain data integrity. Use the other options with caution.

  8. Click OK, and save the table.

Learn more about Data types for Access apps.

Top of Page

Create a values list field

Instead of using an existing field or a query as the source, you can create a list of values.

  1. In the Access desktop program, open the table in Design view.

    Important: In a web app, you need to use a new field. You won't be able to save changes to the table's structure if you change a field's data type to Lookup, even if the field has never contained any values.

  2. Add a new field, and choose Lookup as the data type. This starts the Lookup Wizard.

  3. Click the I will type in the values that I want box.

  4. Enter the values, one on each line, then click OK.

Top of Page

Comparing Combo box and autocomplete controls

In the following view:

Typing in an autocomplete box

  • The Shipping Zone field uses an autocomplete control. As you start typing the first or the first few letters of a zone, you jump to the first match in the list. Use an autocomplete control when people know already what they're looking for, and don't want to have to scroll through a long list. For more information, see Add an autocomplete control to a view.

  • The State field uses a combo box control. Click the down arrow to see the complete list of values. Use a combo box when people need to see all the choices from the list right away. For more information, see Add a combo box to a view.

Top of Page

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×