Microsoft KB Archive/928023

Knowledge Base

A new empty row is added to the One table when you add a row to the Many table of an updatable One-to-Many query in Access 2007

Article ID: 928023

Article Last Modified on 10/15/2007

---

APPLIES TO

• Microsoft Office Access 2007

---

SYMPTOMS

When you add a row to the Many table of an updatable One-to-Many query in Microsoft Office Access 2007, you may experience one or both of the following symptoms:

• A new empty row is also added to the One table.
  
  For example, if you add a new product row to the Products table, a new empty row is also added to the Categories table. The ID number of the new empty row in the Categories table is added to the Join field in the Products table.
• If you try to change the value of the ID number in the Join field of the Many table during the same query edit procedure, you receive the following error message:

  The current field must match the join key '?' in the table that serves as the 'one' side of one-to-many relationship. Enter a record in the 'one' side table with the desired key value, and then make the entry with the desired join key in the 'many-only' table.

Note If you do not add rows to the Many table, the query functions correctly.

This problem does not affect reports.

CAUSE

This problem may occur if the following conditions are true:

• You create an updatable query in Access 2007.
• The updatable query contains a join predicate.
• The join predicate joins a One table to a Many table.
• The output list from the updatable query includes a complex field.
• The complex field is projected from the One table.

WORKAROUND

To work around this problem, use one of the following methods:

• Do not add the complex field from the One table. This problem does not occur if you project a non-complex field from the One table.
  

To obtain data from a complex field on the One table, add a subform to a main form. Set the LinkMasterFields property and the LinkChildFields property on the subform to the Join fields, and then display the complex field on the subform.

• Prevent users from accidentally adding rows to the One table in this query.
  

To do this, set the Required property to TRUE on at least one field that is not the primary key.

Note This workaround also prevents the user from adding rows to the Many table in this query.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Keywords: kbtshoot kbprb kbexpertiseinter KB928023

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.