Dim Cancel as integer

Question

I was writing code to delete data with the option to cancel using vbYesNo

The 'cancel = True' - worked fine below in form1 which did not have any other cancel code. 

Private Sub btnDeleteData_Click()

Dim msg As String

msg = MsgBox("You are about to delete data.  Are you sure you want to continue?", vbYesNo, "Data Delete Message")

If msg = vbNo Then

cancel = True

ElseIf msg = vbYes Then

Me.FIL_NME_FST = Null

MsgBox "Remember to save the Record to accept the deletions. Or click escape to Undo.", vbInformation, "Remember!!!"

End If

End Sub

---

But the same code did not work in other form2 which had subs containing

Private Sub xyz_BeforeUpdate(cancel As Integer)

cancel = True  - got compile error - variable not defined. 

so I wrote - Dim cancel As Integer........Then the code worked fine in form with with xyz_BeforeUpdate(cancel As Integer) subs.

Can someone explain the logic of why  cancel = True  - got compile error - variable not defined in Form2 before I dimensioned it

but not Form1??  Is it just because form1 did not have any other Cancel as integer statements?

Thanks.

Answer 1

But it seems you are saying that 'vbNO' is a default to stop or Not do the Yes actions.

  

Exactly.  If Yes is answered at the prompt and you test for the return value of the MsgBox function being vbYes, then whatever you specify to be done will be done, otherwise it won't be done.  You only need to test for the return value being vbNo if you want something else specifically done if the user answers No.

I realise you are not try to delete a record.  I only included my code to illustrate a situation where setting the return value of a Cancel argument to TRUE is legitimate, not as code suitable for what you are doing.

Answer 2

Ken - You are correct.  The Form1 did not have 'option explicit' in the declaration section.  When I added 'option explicit' the 'cancel=true' is an undefined variable until I Dim the cancel as integer. 

  I may have used the term 'delete' incorrectly.  The record is NOT being deleted -only a few control fields are being reset to 'NULL' when a hiring action begins a second iteration.  e.g., if/when a selectee does not accept an offer the hiring/recruiting process could begin again - so several steps and dates need to be reset to blank/null for the next hiring/recruiting iteration.  I tested the code after removing the 'If msg = vbNo Then'cancel = True' lines and it works as you said.  I thought I had to explicitly state a command or action if the user chooses 'NO'.  But it seems you are saying that 'vbNO' is a default to stop or Not do the Yes actions. 

Thanks for the example code but I am Not deleting the record at a button click.  And the 'dirty event' may not apply since the record is not being edited at the time of the button click.  Also, since the example uses  Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer).... I think the cancel is dim'd/defined as integer in the top line of the sub.

Thanks for your help.  Sorry about delayed reply.  I was enjoying Presidents Day Holiday on Monday.

Answer 3

It's a little surprising that the code worked in any circumstances as the Click event procedure does not have a Cancel argument.  The error is understandable therefore, as is the solution of declaring the variable   I can only assume that the class module of the form in which it is working is missing the Option Explicit line from its declarations area, removing the necessity to declare variables.

More to the point, however, is that it is not necessary to include the Cancel variable in the procedure at all as it does nothing.  You appear to be not actually deleting  a record here, but assigning a Null to a FIL_NME_FST control to remove one item of data only, so if the user responds No to the prompt a Null will not be assigned to the control, so in effect the 'deletion' will be cancelled without having do anything else.

So just remove any reference to the Cancel variable from the procedure and it should work as intended.

For an example of where you would set a return value for a Cancel argument, the following is some code from a form's module which deletes the current record at a button click.  Note how the return value of the form's BeforeDelConfirm is set to True if the deletion is not confirmed.

The button's event procedure firstly undoes any current edits if the record is unsaved.  It then attempts to delete the record, but this might not be possible if the record is a new one which has never been saved, so the error handling detects and ignores this.

One thing about this code which might not be apparent at first sight is that the BeforeDelConfirm event procedure executes immediately after the line RunCommand acCmdDeleteRecord in the button's event, and the code execution in the button's event procedure then resumes, so the DELETE_CANCELLED error is a result of the user not having confirmed the deletion in the BeforeDelConfirm event procedure, not the user's response to the earlier prompt in the button's event procedure.

Private Sub cmdDelete_Click()

    Const DELETE_CANCELLED = 2501

    Const CANT_DELETE = 2046

    Const MESSAGETEXT = "Are you sure you wish to delete the current record?"

    If Me.Dirty Then

        If MsgBox(MESSAGETEXT, vbYesNo + vbQuestion, "Confirm") = vbYes Then

            Me.Undo

        End If

    End If

    On Error Resume Next

    RunCommand acCmdDeleteRecord

    Select Case Err.Number

        Case 0

        ' no error

        Case DELETE_CANCELLED

        ' anticipated error, so ignore

        Case CANT_DELETE

        ' anticipated error, so ignore

        Case Else

        ' unknown error, so inform user

        MsgBox Err.Description & " (" & Err.Number & ")", vbExclamation, "Error"

    End Select

End Sub

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)

    Const MESSAGETEXT = "Are you sure you wish to delete the current record?"

    If MsgBox(MESSAGETEXT, vbYesNo + vbQuestion, "Confirm") = vbNo Then

        Cancel = True

    End If

    Response = acDataErrContinue

End Sub