About AutoLookup queries that enter data automatically

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 field in the query or in a form, report, or data access page 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 Microsoft Access enter the rest of the information for that customer. If no matching information is found, Microsoft Access displays an error message when the focus leaves the record.

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.

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

While you can always update the join field from the "many" side of a relationship, 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, Microsoft Access automatically recalculates any totals or expressions in the query that are dependent on the updated data.