ACC: Error Entering/Editing Linking Field in Multitable Query

• Microsoft Access versions 1.0, 1.1, 2.0

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)

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