riktek
Member
- Local time
- Today, 06:42
- Joined
- Dec 15, 2023
- Messages
- 64
This is a new one.
I'm encountering the error "You cannot add or change a record because a related record is required in table [X]".
The first question is why would this be? In my experience, FK field values are optional, so this comes as a bit of a surprise.
The second question is what can I do to avoid this behavior?
The case is this: A newly developed database has two tables related by non-PK fields in each. The lookup table is populated with records and the main table is not. The PK fields of both tables are AutoNumber Long. The FK fields of both tables (by which the two tables are joined) are text, length 255, AllowZeroLength = No, and Required = No.
The populated table's field indexed without duplicates. Access would not create the relationship otherwise. The empty table is indexed with duplicates OK. The relationship is defined to enforce referential integrity. Access presumes to assign the respective "one" and "many" sides but does so as it would any other lookup table.
Under a seemingly identical configuration (albeit to the lookup table's PK, and by fields typed Long), FK values are optional.
A third question is whether changing the lookup table's PK field (while retaining the AutoNumber field, indexed for no duplicates) would make a difference.
Any thoughts would be greatly appreciated.
I'm encountering the error "You cannot add or change a record because a related record is required in table [X]".
The first question is why would this be? In my experience, FK field values are optional, so this comes as a bit of a surprise.
The second question is what can I do to avoid this behavior?
The case is this: A newly developed database has two tables related by non-PK fields in each. The lookup table is populated with records and the main table is not. The PK fields of both tables are AutoNumber Long. The FK fields of both tables (by which the two tables are joined) are text, length 255, AllowZeroLength = No, and Required = No.
The populated table's field indexed without duplicates. Access would not create the relationship otherwise. The empty table is indexed with duplicates OK. The relationship is defined to enforce referential integrity. Access presumes to assign the respective "one" and "many" sides but does so as it would any other lookup table.
Under a seemingly identical configuration (albeit to the lookup table's PK, and by fields typed Long), FK values are optional.
A third question is whether changing the lookup table's PK field (while retaining the AutoNumber field, indexed for no duplicates) would make a difference.
Any thoughts would be greatly appreciated.