Microsoft KB Archive/96587

= ACC: Error Entering or Editing Linking Field in Multitable Query =

Article ID: 96587

Article Last Modified on 5/9/2003

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q96587



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



SYMPTOMS
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 ' ' (in Microsoft Access version 1.x)

  -or-

There is no record in table ' ' with key matching field(s) ' ' (in Microsoft Access version 2.0)

In the sample messages above, identifies the foreign key and 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 &quot;many&quot; side, and a corresponding match based on the join field does not exist on the &quot;one&quot; 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 &quot;many&quot; table that have matches in the linking field in the &quot;one&quot; 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 &quot;many&quot; table is also known as the &quot;foreign key.&quot;) If a field is updated on the &quot;many&quot; side of a relationship and does not have a match on the &quot;one&quot; side of the relationship, an &quot;orphan&quot; 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: kbprb kbusage KB96587

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.