Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. 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

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

View products that this article applies to.

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.

↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

Status

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

↑ Back to the top

Keywords: KB928023, kbexpertiseinter, kbprb, kbtshoot

↑ Back to the top

Article Info
Article ID : 928023
Revision : 2
Created on : 10/15/2007
Published on : 10/15/2007
Exists online : False
Views : 276