ACC2: "#Name?" Error with SQL Server Tables and Main/Subform (129464)

SYMPTOMS

If you move to a new record on a main form when both the main form and its subform are based on attached SQL Server tables, you may receive the following error message:

Duplicate value in index, primary key, or relationship. Changes were unsuccessful.

You receive this message even if you do not enter a duplicate value. Following the error message, the records on the subform are populated with the "#Name?" error message.

CAUSE

This behavior can occur when you set both the LinkMasterfields and LinkChildFields properties of the main form's subform control to linking fields that have the Integer data type and do not allow null values.

RESOLUTION

To work around this behavior, change the subform's linking field so that it accepts null values in the underlying table. Even though the table accepts null values, you can prevent users from entering them using one of the following methods in the main/subform:

Use Access Basic code in the subform's BeforeUpdate event procedure to check for null values in the linking field.
Create an insert trigger in the SQL server database to check for null values in the field before inserting the record.