Previously 'Required' Fields Still Require Values To Create A New Record

[Dal]

I changed the fields in the relating table to no longer be required. Despite this when initializing a new record in the consecutive form I receive a run-time error '3201 You cannot add or change a record because a related record is required...'. This is untrue, the fields are not set to required. There are relationships; one-to-many; with the many side being where the record is being created but I cannot see how this changes things; relationships do not require population of corresponding fields.



There are events relating to two of the three fields but these events are not fired. What could be causing this? As for the third field (relating to a separate table completely) with no events whatsoever this proves this is not the issue. It is as if the db engine has not updated. A combobox inellisence reference failed to update the other day after renaming it; it kept appearing as it's old name.

[Welshgasman]

Yes, they do. You need a parent for a child record. That is what the error message is telling you. Nothing to do with a required field for a record

[CJ_London]

relationships do not require population of corresponding fields.

All depends if you are enforcing referential integrity - if you are then it is required, if not then you are not really setting a relationship, just a 'join template'.

There is a way you can enforce referential integrity and enter child records without a parent, and that is to set the FK default to null - usually that is the default value when creating a text or number field. If you want to ensure a child record cannot be created without a parent, set the default to 0, -1 or some other value that will never be a valid FK value (and don't allow the situation where a user can change the value to null) - or just set the required property to true

One other tip, if you have a system where you will have numerous null values for a FK, modify the index to ignore nulls - index will be smaller and consequently faster but probably not that noticeable on a few 100k of records

[Dal]

you can enforce referential integrity and enter child records without a parent, and that is to set the FK default to null

High calibre input; thank you. I'm using RI everywhere. I thought i read a Microsoft article stating populated fields were not required & it seemed quite simplistic for it not to be possible.

[CJ_London]

I use that method for cash accounting (which does not use accruals and prepayments) - any entry with a null fk is a simple way to identify pending cash movements