Trap Key Violation on a Delete Query

[Steve]

I have delete query in a form module that can potentially produce a key
violation because it would delete the table's PK which is used as a FK in
another table and referential integrity is enforced. My normal error trapping
method does not work. The only error code the sub produces is zero (I used a
msgbox to display it), but it is definitely producing a key violation and the
deletion is not performed. Any suggestions on how to trap this error so that
I can control how the users react to it? My code is below.

thanks
Steve

Private Sub cmdDelete_Click()
Dim MyResponse As Integer, MyMessage As String
On Error GoTo ErrHandler
MyResponse = MsgBox("Are you sure you want to delete the selected
therapist?", vbInformation + vbYesNo, "Delete therapist?")
If MyResponse = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblTherapists WHERE TherapistEmail = '" &
Me.lboTherapists & "'"
Me.lboTherapists.Requery
DoCmd.SetWarnings True
Exit Sub
Else
Exit Sub
End If
ErrHandler:
'Error code goes here
End Sub

 

[John Spencer (MVP)]

Well you could include code to see if the Therapist was used in the other table.
If DCount("*","SomeOtherTable","Fk = " & TheTherpaistPKFieldValue) <> 0 Then
Msgbox "Records exist in ??? table for this therapist." & _
"You must delete those records first"
Else
'Delete the therapist record
End if

Or you could use the execute method and trap for an error.

Private Sub cmdDelete_Click()
Dim MyResponse As Integer, MyMessage As String
Dim dbAny as DAO.Database
Dim strSQL as String

On Error GoTo ErrHandler
MyResponse = MsgBox("Are you sure you want to delete the selected
therapist?", vbInformation + vbYesNo, "Delete therapist?")
If MyResponse = vbYes Then
DoCmd.SetWarnings False
Set dbany = CurrentDb()
strSQL = "DELETE * FROM tblTherapists WHERE TherapistEmail = '" & _
Me.lboTherapists & "'"
dbAny.Execute StrSQL, dbfailonError
Me.lboTherapists.Requery
DoCmd.SetWarnings True
Exit Sub
Else
Exit Sub
End If
ErrHandler:
'Trap the relevant error and do your processing.
IF Err.Number = ??? THen
Msgbox "Records exist in ??? table for this therapist." & _
"You must delete those records first"
end if
'Error code goes here
End Sub

OR finally, you could set up a relationship between the two tables and use
Cascade delete to automatically (no warning) delete the related records. This
is not usually a good idea, but sometimes it is exactly what you want.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Steve]

The DCount function worked. I was stuck on the thought of trying to capture
an error number, but the only one that ever came up was zero. I have the
cascade deletes turned off because I don't want that to happen. Thanks for
the help!

Steve