Microsoft KB Archive/291553

= Example of row fix-up in the Northwind orders form in Access 2002 =

Article ID: 291553

Article Last Modified on 8/11/2004

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q291553



Novice: Requires knowledge of the user interface on single-user computers.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



For a Microsoft Access 97 version of this article, see 95643.

For a Microsoft Access 2000 version of this article, see 209672.



SUMMARY
Microsoft Access provides an automatic &quot;row fix-up&quot; feature that uses links between tables to update (fix up) records in queries and forms. You can use this feature to enter a single value, and then Microsoft Access automatically looks up and displays other values for that record.

Row fix-up enables you to update forms and queries without using macros or code. This article uses the Orders form in the sample database Northwind.mdb or the sample Microsoft Access project NorthwindCS.adp to demonstrate how to set up and use row fix-up.

NOTE: The row fix-up technique is referred to as AutoLookup in Microsoft Access.



MORE INFORMATION
To see an example of the row fix-up technique, follow these steps:  Open the sample database Northwind.mdb or the sample Access project NorthwindCS.adp. Open the Orders form in Design view. If it is not displayed, open the property sheet for the form. In the property sheet for the form, click the Data tab, and then click the Build (...) button next to the RecordSource property box. Note the following about the RecordSource property of the Orders form:  The record source is the Orders Qry query or view.  The Join property between Customers and Orders tables is: <pre class="fixed_text">  One                          Many -  CUSTOMERS.[CustomerID] ==>  ORDERS.[CustomerID] </li> The CustomerID field in the query/view grid comes from the Orders table, which is on the &quot;many&quot; side of the relationship, and not from the Customers table, which is on the &quot;one&quot; side of the relationship. (See point 4b.)</li></ol> </li> Close the Orders Qry query or view, and then click the Bill To combo box. Look at its RowSource property in the property sheet. The row source is an SQL Select statement. Click the Build (...) button, and note the following about the RowSource query: <ol style="list-style-type: lower-alpha;"> The query is based on the Customers table.</li> The query returns two columns: CustomerID and CompanyName.</li> The bound column is CustomerID.</li> The visible column is CompanyName.</li></ol> </li> Microsoft Access select queries are updatable. This means that when you make a change to a query's recordset, Microsoft Access can update the source tables with your changes.</li></ol>

The Bill To combo box presents you with a list of company names from the visible column of its RowSource query. When you select a company name, the combo box stores the bound column--the CustomerID field from the RowSource query--in the CustomerID field in the Orders table. (See points 5c and 5d.) It is important to note that the selection is stored in the many-side table. (See point 4c.)

After the combo box updates the CustomerID field in the Orders table (the many-side table), the field is changed so that it no longer points to the same record in the Customers table (the one-side table). (See point 6.) Note that the Orders table is updated because the query's recordset was changed.

Microsoft Access recognizes that the relationship no longer matches; therefore, it automatically updates the relationship by linking the changed record in the Orders table with the appropriate record in the Customers table.

All the fields in the Orders form that come from the Customers table (the one-side table) are updated to show the values in the record for the newly formed link.

Example
When you make a change to the Bill To combo box, Microsoft Access uses row fix-up to update the following fields on the Orders form:
 * [Address]
 * [City]
 * [Region]
 * [PostalCode]
 * [Country]

The following fields on the Orders form are not updated by row fix-up. However, they are updated by the AfterUpdate event attached to the Bill To combo box:
 * [ShipName]
 * [ShipAddress]
 * [ShipCity]
 * [ShipRegion]
 * [ShipPostalCode]
 * [ShipCountry]

<div class="references_section">