About AutoLookup queries that automatically fill in data (MDB)

 Note   The information in this topic applies only to a Microsoft Access database (.mdb).

You can design a multiple-table query to automatically fill in certain field values for a new record. When you enter a value in the join (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) field in the query, or in a form, report, or data access page (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.) based on the query, Microsoft Access looks up and fills in existing information related to that value. For example, if you know the value in the join field between a Customers table and an Orders table (typically, a customer identifier such as CustomerID), you could enter the customer ID and have Access enter the rest of the information for that customer. If no matching information is found, Access displays an error message when the focus leaves the record.

Enter a value in a join field to retrieve remaining data

Callout 1 Add a new order including the Customer ID, and then press ENTER.

Callout 2 Access fills in the rest of the customer information.

ShowPrerequisites for creating an AutoLookup query

For AutoLookup to work, certain conditions must be met:

ShowWhat happens when you update data

When the value of the join field from the "many" side of the relationship is added or changed in a record, Access automatically finds and displays the associated values from the table on the "one" side of the relationship.

You can always update the join field from the "many" side of a relationship, but you can update the join field from the "one" side only if you enabled cascading updates (cascading update: For relationships that enforce referential integrity between tables, the updating of all related records in the related table or tables when a record in the primary table is changed.) when defining the relationship between the tables. Either way, when you update data, Access automatically recalculates any totals or expressions in the query that are dependent on the updated data.

Applies to:
Access 2003