Mandatory FK values? "You cannot add or change a record because a related record is required in table"

[Pat Hartman]

Thank you for confirming my points.

One way to identify ZLS in your BeforeUpdate event validation code is:

If Me.Somefield & "" = "" Then ''' field is either null or contains a ZLS - both are invalid when a field is required.

 

[Isaac]

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 guessing your interpreting it wrong.

It's the parent table that's missing a record, not the child. That's to be expected.

 

[Pat Hartman]

Re-creating this second relationship characterizes it as 1:1

That means that you have the PK pointing to another PK instead of a FK. 1:1 relations are extremely rare in the real world and you would NEVER have one with a lookup table. If you do create a 1:1 relationship, only one of the PKs can be an autonumber. The other has to be a long integer so you can ensure that the two keys match. If you define them both as autonumbers, you will run into a problem if they ever get out of sync.