"You cannot add or change a record because a related record" error

[metrodub]

I have a data entry form for course creation that contains a few comboboxes (row sources are from respective tables and the control source is the respective foreign key ID in the main table (tblClass)).

If an end user wants to begin data entry for a particular course, but doesn't have the information on the location of the class (cboLocation) and, of course, doesn't have facility or room info (cboFacility, cboRoom), they have an option to save the record for later changes/updates.

When they do this (by clicking on a command button) an error pops up stating "You cannot add or change a record because a related record is required in table 'Facilities'" (for example).

Is there a way to set the properties or write some code so that the end user doesn't get this error?

 

[PHV]

You have to uncheck the referential integrity in the relationship window.
Perhaps allowing null for the FK fields will suffice.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

...but you will have to watch out for lost / orphan records.

Another work-around solution is to include an "Unknown" record for the Facility, Location, Class, Room. Default to the "Unknown" record (can set at the Table level in the design).

This work-around accomplishes two things...
- referential integrity is presered
- the end user has a visual reminder that various objects have not yet been chosesn.

You may have a bonus too. Say a course on Access Design at Location: Admin, Room: 101 has to be rescheduled after registration because the room is too small. You can change the Room to "Unknown" -- much better than leaving it as room 101 because "Unknown" is a definite red flag.

Richard

 

[metrodub]

Thanks for all of the suggestions. I had resorted to using "Unknown" or "None" for some of my comboboxes.

I then found out why I was getting this error. Each of my FKs were indexed in my main table. For some reason, by being indexed, data needs to be inputed in order to save the record. I chose to have each FK to not be indexed and everything is working.

Again, thanks for the suggestions.

 

[willir]

You have to uncheck the referential integrity in the relationship window.
Perhaps allowing null for the FK fields will suffice.

By removing the indexes, you have probably accomplished the same thing PHV suggested. If the solution works -- great, but watch out for orphans, and you may experience performance problems if you database grows.