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

ID: Q95048

The information in this article applies to:

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:

Q93138 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

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

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).

Add the following fields from the Products table:

Product ID
Product Name
Category ID

Add the following fields from the Categories table:

Category Name
Description

Save the query as AutoLookup Example.

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.

This process works because the Category ID field located in the query comes from the many-side table (Products). Microsoft Access performs AutoLookup on one-side tables when you enter identifying data in the many-side table. When you enter a valid value in the Products table's Category ID field, Microsoft Access knows that Category ID is the key field for the one-side table (Categories), so it looks up the information from Categories based on the Category ID value and automatically displays it in the query.

Example 2: Create a Form That Uses AutoLookup

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

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.

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.

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.

Note that if you had included the primary key value from the one-side table (Categories), you would not have been allowed to add new records to the query or to change the Category ID value. For this reason, you must drag the Category ID field from the Products table, rather than from the Categories table.

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"

Additional query words: how to \* \* Note that this article is referred to by the SRACCESS \* article SRX950924000012. If you modify or delete this article, \* please make the corresponding changes to that article.


Keywords          : kbusage QryJoin 
Version           : 1.0 1.1 2.0
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: March 19, 1999