Alert/Message boxes for delete queries (1 Viewer)

[Judy]

I have a form on which I added a button to delete a record by running a delete query. Instead of the pre-defined message boxes (you are about to run a query... and you are about to delete 1 record...) I would like to supress those messages and show a message only if deleting the record violates referential integrity (You cannot delete this record until related records are removed.)

My code is as follows - notice I have set warnings false, then after the docmd.openquery statement I set them to true again.

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click

Dim stDocName As String
Dim strMessage As String

stDocName = "qryDeleteRecord"
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True

Exit_cmdDelete_Click:
Exit Sub

Err_cmdDelete_Click:

Resume Exit_cmdDelete_Click

End Sub

Does anyone know how to generate a different message such as above?

Thanks for your help!

Judy

 

[Judy]

Funny, but I answered my own question! Just in case anyone cares, this is the solution I came up with:

Dim stDocName As String
Dim strMessage As String
Dim intRecords As Integer

' Hide query messages and customize referential integrity message
' and confirmation of deleted record

intRecords = DCount("[qyrCountRecords]", "Name", "[RecordID] = '" & Me![RecordID] & "'")
If intRecords > 0 Then
strMessage = MsgBox("Related records must be changed before this record can be deleted.", vbOKOnly, "Information")
Else
stDocName = "qryDeleteRecord"
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acNormal, acEdit
Me.Requery
DoCmd.SetWarnings True
strMessage = MsgBox("Record has been deleted.", vbOKOnly, "Information")
End If

Thanks anyway and happy holidays!