The information in this article applies to:
- Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
SUMMARY
Novice: Requires knowledge of the user interface on single-user computers.
Microsoft Access provides an automatic "row fix-up" 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 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 NWIND.MDB in versions 1.x and 2.0) 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 2.0, 7.0 and 97, and as Dynamic Lookup in Microsoft Access 1.x.
MORE INFORMATION
To see an example of the row fix-up technique, follow these steps:
- Open the Northwind.mdb sample database (or NWIND.MDB in versions 1.x
and 2.0).
- Open the Orders form in Design view.
- Select the RecordSource property on the Orders form's property sheet
and click the Build button. (In Microsoft Access 1.x, open the Order
Information query in Design view.) Note the following about the
RecordSource property of the Orders form.
a. In Microsoft Access 97, the record source is the Orders Qry query.
In version 7.0, the record source is an SQL Select statement. In
version 1.x and 2.0, the record source is the Orders Information
query.
b. The Join property between Customers and Orders tables is:
One Many
-------------------------------------------------
CUSTOMERS.[CustomerID] ==> ORDERS.[CustomerID]
NOTE: In versions 1.x and 2.0, there is a space in Customer ID.
c. The OrderID field in the query grid comes from the Orders table,
which is on the many side of the relationship, and not from the
Customers table, which is on the one side of the relationship. (See
point 3b, above.)
NOTE: In versions 1.x and 2.0, there is a space in the Order ID
field.
- Close the RecordSource query and click the Bill To combo box. Look at
its RowSource property on the property sheet. In Microsoft Access 7.0
and 97, the row source is an SQL Select statement. In versions 1.x and
2.0, the row source is based on the Customer List query. Click the
Build button and note the following about the RowSource query:
a. The query is based on the Customers table.
b. The query returns two columns: CompanyName and CustomerID.
NOTE: In versions 1.x and 2.0, there is a space in Company Name
and Customer ID.
c. The bound column is CustomerID.
d. The visible column is CompanyName.
- 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.
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 4c and 4d,
above.) It is important to note that the selection is stored in the many-
side table. (See point 3c, above.)
After the combo box updates the CustomerID field in the Orders table
(the many-side table), the field is changed so it no longer points to
the same record in the Customers table (the one-side table). (See point
5, above.) 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]
- [ShipPostal]
- [ShipCountry]
REFERENCES
For more information about row fix-up, search the Help Index for "row fix-
up."
For more information about updatable queries, search the Help Index for
"queries, modifying results."