one-to-many delete custom error message

[Guest]

If the user tries to delete the Invoice (main form with a sub-form with the
details), Access displays the following message: The record cannot be deleted
or changed because table 'InvoiceDetail' includes related records.

Is there a way to display a more friendly message so the user knows what is
happening instead of the access error message?

Thank you for your help!!!

 

[Allen Browne]

Use the BeforeUpdate event procedure of the form if you wish to display your
own message.

You do not hae information about the record(s) being deleted in
Form_BeforeUpdate, so if you want to provide more specific information you
must use Form_Delete to store the key values of the record(s) being deleted.
Then in Form_BeforeUpdate you can use DCount() to get the number of related
records and include that info in the custom message.

Set the Response argument of Form_BeforeUpdate to suppress the built-in
error message after displaying your own.

 

[Allen Browne]

So you just want to look up another table, and block the deletion if a
related record is found there?

Private Sub Form_Delete(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
strWhere = "[ForeignID] = " & Me.[ID]
varResult = DLookup("ForeignID", "Table2", strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "Ya can't do that!"
End If
End Sub