Error 3201 You can't add or change a record...when delete/deleting

[matchorno]

Hi. I have two tables, one called tblExpungements and one called
tblProsecutors.

tboProsecutors has primary key called ProsecutorID, then other fields such
as ProsecutorFName, ProsecutorLName, etc.

tblExpungements has a foreign key called ProsecutorID which is linked to the
tblProsecutor table.

I have a form based on tblExpungements. There is a drop down box on this
form for the foreign key field: ProsecutorID which looks up the Prosecutor's
name in the tblProsecutors table.

Everything works fine when you fill in the form completely and save it.
However, the problem I'm having is when someone starts to fill in the form,
but decides to delete it before all fields are filled in. If the
ProsecutorID combobox field hasn't been filled in yet, I get the following
error when trying to delete the record:

Error 3201 You can't add or change a record because a related record is
required in table tblProsecutors.

So in order to delete the record, you have to first choose a prosecutor from
the combobox, then you can delete the record.

The field is set to Not Required...so why won't it let me delete the record
unless I fill in this field? I figure I could trap the error and have the
code me.undo run before it deletes it...but it doesn't seem right to have to
do this. I might not want to undo everything if the error arises because of
a different reason.

I scoured all kinds of forums, but people seem to usually get this error
when they are trying to save a record but there is some sort of referential
parent/child problem. Not when they are just trying to delete the record. I
believe my relationships are sound. Let me know if you need any
clarification on the problem. Thanks for your help.

 

[Jerry Whittle]

Check out the relationships window. I bet that the tblProsecutors is the
parent in a 1-to-many relationship.

To get around this problem you could create a bogus Prosecutor and make it
the default value in the tblExpungements ProsecutorID field. The worry there
is that somebody won't put in a 'real' Prosecutor and that will mess up some
records.

 

[matchorno]

Nevermind...figured it out. The foreign keys had a default value of 0 (the
default when creating a table). When I deleted the default value out of the
table design, the record now seems to delete fine without the error.
Although this default value of "0" wasn't displayed in the combobox on a new
record, it must have been trying to assign that to the foreign key
somehow?...and there wasn't a matching record of "0" in the
tblProsecutorsTable. I think that's why, not exactly sure...but it works.
Thanks.