ACC: AutoLookup Technique to Look Up Info. Automatically 1.x/2.0

• Microsoft Access versions 1.0, 1.1, 2.0

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

You can create a query or form that automatically looks up information in the one-side table of a one-to-many relationship based on an entry in a many-side field. This process is sometimes called AutoLookup.

You can use AutoLookup to have a query automatically look up information in other tables and display it on a form or report.

You can look up information by using any one of the following three techniques:

• Use AutoLookup. For more information on AutoLookup, see the "More Information" section below.
• Use multiple DLookup() functions in forms and reports.
• Use the Column property of a multiple-column combo box to update a text box control with new information as focus moves from row to row in the combo box. For more information about using the Column property, please see the following article in the Microsoft Knowledge Base:

        ARTICLE-ID: Q93138
        TITLE     : ACC: Using the Column Property to Update Text Box
  
  

MORE INFORMATION

Use AutoLookup when you want Microsoft Access to automatically look up information in the one-side table based on an entry in a many-side field. The AutoLookup technique works in a query or in a form. AutoLookup also works with tables that have a one-to-one relationship if you use a left outer join. For more information about what data is updatable, search for "Underlying table or query," then "When is a query updatable?" using the Microsoft Access Help menu.

For example, the Categories and Products tables in the sample database NWIND.MDB have a one-to-many relationship. Each category may appear several times in the Products table. The Products table contains the Category ID field, which is the foreign key that identifies the category for a product.

When AutoLookup updates records, Microsoft Access automatically recalculates any totals or expressions that are dependent on the updated information.

Microsoft Access version 1.1 has additional online Help topics for AutoLookup issues. In this context, the term "AutoLookup" is synonymous with the term "dynamic lookup." For more information, search for "dynamic lookup", then "Displaying Fields from Another Table or Query (Common Question)" using the Help menu. In Microsoft Access version 2.0, search for "AutoLookup."

The following two examples demonstrate how AutoLookup works:

Example 1: Create a Query That Uses AutoLookup

1. Start Microsoft Access and open the sample database NWIND.MDB.

2. Create a new query. Add the Products and Categories tables. Products is the many-side table and Categories is the one-side table. The two tables have a many-to-one relationship based on the Category ID field (meaning there are many identical Category ID values in Products for each unique Category ID value in Categories).

3. Add the following fields from the Products table:

         Product ID
         Product Name
         Category ID
   

4. Add the following fields from the Categories table:

         Category Name
         Description
   

5. Save the query as AutoLookup Example.

6. View the results of the query. To verify that the query performs AutoLookup, move to the end of the query and create a new record. Enter a valid Category ID value in the Category ID field. Microsoft Access will automatically look up the values associated with that Category ID value in the Categories table.

Example 2: Create a Form That Uses AutoLookup

1. Create a new form based on the query that you created in Example 1.

2. Locate the fields from the Products table on "one" side of the form and those from the Categories table on the "many" side of the form.

3. Create a combo box bound to the Category ID field. Set the RowSource property to the Categories table. Display a single column, [Category ID], in the combo box.

4. Switch the form to Form view. When you enter new records and select a category from the Category ID combo box, all related information about that Category is displayed.

REFERENCES

Microsoft Access "User's Guide," version 1.0, Chapter 10, "Creating Forms Based on More than One Table," page 282

Microsoft Access "User's Guide", version 2.0, Chapter 11, "Designing Select Queries"