Handling SQL errors from constraints

In a Base Form, I want to give the users a “remove” button for errantly created records in a subform. Thing is, the records’ primary key is referenced by foreign keys in other tables. Since foreign key checks will be enabled, deleting referenced records will be prevented, which is what I want, but I want to be able to handle the error by presenting the user with a non-cryptic explanation. Is there a good reference on how to handle a SQL error from foreign key constraints? I know there’s an option to use an On Error GoTo…Resume" statement, but I want to control the response based on the specific type of error.

Could you give an example for the error, which appears? You have already set the relations and also what should happen on update and deleting?

I want to prevent deletions that would orphan records in other tables. Yes, the relations are set up. I get the error message

Error deleting the specified records

Cannot delete or update a parent row: a foreign key constraint fails (metrology.measurementdata, CONSTRAINT FK_MEASUREMENTDATA_ITEMINWORKORDER FOREIGN KEY (ITEMINWORKORDER) REFERENCES itemsinworkorders (ID))

So you want a message like “Deleting impossible.”?
You will need something like

FUNCTION SaveRequired(oEvent AS OBJECT) AS BOOLEAN
SaveRequired = True
IF … THEN
…
SaveRequired = False
END IF
END FUNCTION

This should run from the form → Before record action. You have to ask for foreign key in a separate query.

Thanks! Sounds like a promising direction. Would there be a way to ensure that the record action is a deletion before showing the message? The form is supposed to be used for additions. And the delete option I’m adding is only for situations where errant additions have been made.

Have tested with XRay:
oEvent.Action might be ‘3’ for deleting a record.
Seems to be ‘2’ for update and ‘1’ for insert a new row.