ACC2002: Row Fix-Up Does Not Update Other Rows When Data on the Primary Side Is Edited (282348)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q282348
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access project (.adp).

SYMPTOMS

When you create a query (whether it is a function, a view, or a stored procedure) based on a one-to-many relationship between two tables, and then you edit data in one record of the primary table (the "one" side), the changes are not propagated as expected to the same field in other rows of the view.

CAUSE

This behavior occurs because the query does not include all key fields from the tables on which it is based.

RESOLUTION

To resolve this issue, include all key fields in the query from tables with one-to-many joins. To do this for a view, follow these steps:
  1. Open the sample project NorthwindCS.adp.
  2. Under Objects, click Queries, and then double-click Create view in designer.
  3. In the Add Table dialog box, add Categories and Products, and then click Close.
  4. Add CategoryID from the Categories field list, and then add ProductID and CategoryID from the Products field list.
  5. Save the view, and then click the Run button on the toolbar.
  6. Change the first occurrence of "Beverages" to "Drinks," and then move off the record. Note that all occurrences of "Beverages" have been changed to "Drinks."
The steps for functions and stored procedures are the same.

MORE INFORMATION

Queries based on two tables in a Microsoft Access database that have a one-to-many relationship allow you to edit the data drawn from the principal table (the "one" side) in one row of the query's data and propagate the changes to other rows in which the same data occurs.

In an Access project, queries are more specifically identified as functions, views, or stored procedures, and the same behavior can be obtained, except that it is necessary to include all key fields from the both tables in the relationship.

NOTE: This means that you need to induce not only the primary keys from the separate tables, but the foreign keys as well.

Steps to Reproduce the Problem

These steps describe the behavior of a view; the behavior of functions and stored procedures is the same.
  1. Open the sample project NorthwindCS.adp.
  2. Under Objects, click Queries, and then double-click Create view in designer.
  3. In the Add Table dialog box, add Categories and Products, and then click Close.
  4. In the Categories field list, select CategoryName, and then select ProductName in the Products field list.
  5. Save the view as TestView.
  6. With the new view selected in the Database window, click AutoForm on the Insert menu.
  7. Open the form in Design view and set the Unique Table property of the form to Products.
  8. Open the form in Datasheet view. Under CategoryName, change the first occurrence of "Beverages" to "Drinks," and then move off the record.
Note that other occurrences of "Beverages" are not changed to "Drinks" as expected.
Modification Type:MajorLast Reviewed:11/5/2003
Keywords:kbprb KB282348
©2003 Microsoft Corporation. All rights reserved.