Suppress Cancel Error

[scott]

I use the cmdDelete_Click() sub on a Delete button. When a user clicks the
Delete button, the Form_Delete sub displays a Yes/No message box and gives a
delete confirm message. My problem is if the user selects "No", Access gives
the error "you canceled the previous operation".

How can I suppress the "you canceled the previous operation" error? I've
tried setting the Warnings to False, but that doesn't seem to effect the
problem.

CODE *************

Private Sub cmdDelete_Click()
On Error GoTo cmdDelete_Click_Err

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDelete

cmdDelete_Click_Exit:
Exit Sub

cmdDelete_Click_Err:
MsgBox Error$
Resume cmdDelete_Click_Exit

End Sub

Private Sub Form_Delete(Cancel As Integer)
On Error GoTo Errorhandler

Dim Msg As String, BoxResponse As String
Dim Title As String, Style As Integer
Msg = "Are you absolutely sure you want to permanently delete this
record?"
Style = vbYesNo + vbExclamation
BoxResponse = MsgBox(Msg, Style, Title)

If BoxResponse = vbYes Then
Cancel = False
DoCmd.SetWarnings False
SendKeys "{enter}", False

DoCmd.RunSQL "DELETE * FROM myTable " & _
"WHERE myTable.id=[Forms]![myForm]![id]));"

DoCmd.SetWarnings True
Else
DoCmd.SetWarnings False
DoCmd.CancelEvent
Exit Sub
Cancel = True
End If

Exit_Point:
Exit Sub

Errorhandler:

Select Case Err.Number
Case 2001, 2501 ' "Cancel" error codes
Resume Exit_Point
Case Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End Select

End Sub

 

[TonyT]

Hi Scott,

You have cancel in there twice;
DoCmd.CancelEvent
&
Cancel = True

Cancel = True, before the exit sub on it's own should do it for you.

TonyT..

 

[Graham Mandeno]

Hi Scott

Your error handler, where you check the error code is not 2001 or 2501,
should be in cmdDelete_Click, not in Form_Delete:

Select Case Err.Number
Case 2001, 2501 ' "Cancel" error codes
Resume cmdDelete_Click_Exit
Case Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume cmdDelete_Click_Exit
End Select

The Delete event is being cancelled in Form_Delete, and that status is being
passed back to where the delete was requested (cmdDelete_Click) where the
error is raised.

Also, you can replace your two RunCommand calls with a single one:

DoCmd.RunCommand acCmdDeleteRecord