|
Important Access Services 2010 and Access Services 2013 are being removed from the next release of SharePoint. We recommend you do not create new web apps, and migrate your existing apps to an alternative platform, such as Microsoft Power Apps. You can share Access data with Dataverse, which is a cloud database upon which you can build Power Platform apps, Automate workflows, Virtual Agents, and more for the web, phone, or tablet. For more information, see Get started: Migrate Access data to Dataverse. |
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
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:
-
In the Access desktop program, open the table in Design view.
-
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.
-
Click I want the lookup field to get the values from another table or query.
-
Choose the table or query you want to use as the data source.
-
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.
-
Choose how to sort the values in the Do you want to sort the items in your lookup? list.
-
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.
-
Click OK, and save the table.
Learn more about Data types for Access apps.
Create a values list field
Instead of using an existing field or a query as the source, you can create a list of values.
-
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.
-
Add a new field, and choose Lookup as the data type. This starts the Lookup Wizard.
-
Click the I will type in the values that I want box.
-
Enter the values, one on each line, then click OK.
Comparing Combo box and autocomplete controls
In the following view:
-
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.
