For AutoLookup to work, certain conditions must be met:
- The query must be based on more than one table and the tables must have a one-to-many relationship. (Referential integrity doesn't have to be enforced.)
- The join field on the "one" side of the relationship must have a unique index. A unique index means that the field is a primary key or its Indexed property in table Design view is set to Yes (No Duplicates).
- The join field you add to the design grid must come from the table on the "many" side of the one-to-many relationship. (In other words, it's the foreign key for that table.) For AutoLookup to work, this field can't be a primary key and its Indexed property can't be set to Yes (No Duplicates). For example, in a query that includes the Customers and Orders tables, drag to the design grid the CustomerID field from the Orders table, not the one from the Customers table.
- The value you enter in the join field from the "many" side must already exist in the join field from the "one" side.
What 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 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.