Using AutoLookup

AutoLookup is a feature of the Microsoft Access database engine that allows related fields on a form to display correctly when you change data. It applies if a query includes a one-to-many join and certain output fields. Forms based on queries perform AutoLookup as well (when the AllowUpdating property is set to Default Tables). Although AutoLookup behavior isn't particularly associated with combo or list boxes, it can be used with them to display related data when you select a value in a list. (For an example of this technique, see the "Combo 3" form in AC208.MDB.)

To use AutoLookup, create a query with a one-to-many relationship. Include fields from both tables, but be sure to include the linking value from the "many" table (not the "one" table), since this is the table in which you want to edit it. When you modify this field on the "many" side of the join, the Microsoft Access database engine automatically refreshes the output information fetched from the "one" side of the join. So, for example, if you have a combo box where you select the supplier for a product, when you select a supplier (which sets the Supplier ID value in the Products table), other fields such as the supplier's name and phone number are displayed correctly for the corresponding supplier.

Note that using AutoLookup requires that the one side of the one-to-many relationship be a query field (or a form-field bound to a query field). If you want to use an unbound combo box to select records, then AutoLookup isn't the solution.