one-to-many delete custom error message

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!!!
 
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.
 
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
 
Back
Top