ACC: Error Entering/Editing Linking Field in Multitable Query

ID: Q96587

The information in this article applies to:

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you add or edit records in a form or query datasheet and you attempt to commit the record by moving from the current record or by closing the recordset, you may receive the following error message:

Join is broken by value(s) in fields '<field name>' (in Microsoft Access version 1.x) 

   -or-
There is no record in table '<table name>' with key matching field(s) '<field name>' (in Microsoft Access version 2.0)

In the sample messages above, <field name> identifies the foreign key and <table> identifies the one-side table.


CAUSE

You tried to enter or edit data through a query based on more than one table. In a one-to-many relationship, you entered or edited data on the "many" side, and a corresponding match based on the join field does not exist on the "one" side.


STATUS

This behavior is by design.


RESOLUTION

When you add or edit records in a query, if the relationship between two tables is a one-to-many relationship, only enter values in the linking field in the "many" table that have matches in the linking field in the "one" table.


MORE INFORMATION

Microsoft Access knows that a given relationship is a one-to-many relationship if the field on which the tables are linked or joined is a primary key (or Indexed - No Duplicates) in one of the tables, but not in the other table. (The linking field in the "many" table is also known as the "foreign key.") If a field is updated on the "many" side of a relationship and does not have a match on the "one" side of the relationship, an "orphan" would be produced.

Steps to Reproduce Behavior


  1. Open the query named Order Information.


  2. From the Records menu, choose GoTo, and then select New.


  3. Input a new Order ID and Customer ID. Enter a value in the Customer ID field that does not exist in the Customers table.


  4. Press the DOWN ARROW key to move to a new record. Note that the following error message is displayed:

    Join is broken by value(s) in fields 'Customer ID'



Keywords          : kbusage QryJoin 
Version           : 1.0 1.1 2.0
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: March 20, 1999