vbOKCancel won't cancel the operation

[Guest]

Hello, I'm back again. In the code below, if the user clicks cancel I want
the delete process to abhort. I get the error message I want, but if the user
clicks cancel the record gets deleted anyway. Please Help, Thanks, Robert
************************************************************
Private Sub ComplicationDelete_Click()

Dim sSQL As String

On Error GoTo ComplicationDelete_Click_Error

Select Case MsgBox("PLEAE USE EXTREME CAUTION BEFORE DELETING AN ITEM FROM
THE LIST. PRESS CANCEL TO AVOID DELETING THIS ITEM. IF YOU ARE SURE YOU WANT
TO DELETE THIS ITEM, PRESS OK", vbOKCancel Or vbExclamation Or
vbDefaultButton1, "Remove Item From List")

Case vbOK

Case vbCancel

End Select
sSQL = "DELETE FROM tblVisitComplications WHERE fldVisitComplicationsNo"
& Me.lstVisitComplications.Column(0)
CurrentDb.Execute sSQL
Me.Refresh
DoCmd.GoToRecord , , acNewRec

On Error GoTo 0
Exit Sub

ComplicationDelete_Click_Error:

MsgBox "You Must Highlight An Item To Delete. Use EXTREME CAUTION when
DELETING an item from the list "
End Sub

 

[Rick Brandt]

Hello, I'm back again. In the code below, if the user clicks cancel I
want the delete process to abhort. I get the error message I want,
but if the user clicks cancel the record gets deleted anyway. Please
Help, Thanks, Robert
************************************************************
Private Sub ComplicationDelete_Click()

Dim sSQL As String

On Error GoTo ComplicationDelete_Click_Error

Select Case MsgBox("PLEAE USE EXTREME CAUTION BEFORE DELETING AN ITEM
FROM THE LIST. PRESS CANCEL TO AVOID DELETING THIS ITEM. IF YOU ARE
SURE YOU WANT TO DELETE THIS ITEM, PRESS OK", vbOKCancel Or
vbExclamation Or vbDefaultButton1, "Remove Item From List")

Case vbOK

Case vbCancel

End Select
sSQL = "DELETE FROM tblVisitComplications WHERE
fldVisitComplicationsNo" & Me.lstVisitComplications.Column(0)
CurrentDb.Execute sSQL
Me.Refresh
DoCmd.GoToRecord , , acNewRec

On Error GoTo 0
Exit Sub

ComplicationDelete_Click_Error:

MsgBox "You Must Highlight An Item To Delete. Use EXTREME CAUTION
when DELETING an item from the list "
End Sub

There is no automatic "cancelling effect" when the user presses the Cancel
button on a message box. YOUR CODE has to recognize that they pressed cancel
and do something different compared to when they do not press Cancel.

You're running your delete SQL statement after the entire Select Case block so
it is going to run no matter what option the user takes. You need to run it
only inside the case block for vbOk

 

[Guest]

Thank you Rick, I'd be screwed with out you and the MVP's

Rob