Effect of "Cancel = True"

[riktek]

I've been doing this long enough to both understand the topic and recognize that the understanding I glean often isn't at all what is occurring, notwithstanding a preternaturally rigorous review of the documentation and online commentary.

So it is here, where I'm encountering what seems to me to be anomalous behavior (or what I have come to describe in a fit of self-justification as resistance to configuration).

I'm fiddling with some code in a startup / display form and attempted without success to cancel the Open event. The purpose of this is irrelevant to the question but for the curious, it's simply part of handing processing off to another module. The extent of the startup form's module is:

Option Compare Database
Option Explicit

Private Sub Form_Close()
    Debug.Print Me.Name & vbTab & "Form_Close()"
End Sub

Private Sub Form_Load()
    Debug.Print Me.Name & vbTab & "Form_Load()"
End Sub

Private Sub Form_Open(Cancel As Integer)
    Debug.Print Me.Name & vbTab & "Form_Open()"
    Cancel = True   'Behavior is identical whether before or after this With block:
    With Startup    'Startup is a predeclared class module.
        If Not .Main Then .SetMain
    End With
End Sub

My understanding, not least because of the estimable words [the link to which I'm apparently not permitted to post] of @The_Doc_Man :

If you cancel the Form_Open event by returning -1 or TRUE to the Cancel parameter, then none of the other events occurs because the form does not open. It immediately gets dereferenced by the GUI.

Alas, this does not occur (in Access 2007, to be sure, but still). Imagine one's dismay at then seeing in the Immediate pane:

frmStartup  Form_Open()
        Startup Class_Initialize()
frmStartup  Form_Load()
frmStartup  Form_Close()
frmMain Form_Open()
frmMain Form_Load()

Apparently, ALL of the other events occur (frmStartup's Load and Close, that is), at least the way I'm doing it. The expected behavior, and with some justification (as noted), is that event progression should immediately cease such that no other form events, including Load and Close, would occur. So, canceling Open would prevent Load (not to mention Activate, Current, Deactivate, Unload, and Close); canceling BeforeUpdate would prevent AfterUpdate; etc.

The question is, provided my implementation isn't flawed (always a risk), if event progression does not cease immediately, or at the end of a canceled event procedure, then what happens instead and can event progression otherwise be terminated?

I'm missing something, clearly.

 

[The_Doc_Man]

I would have to suggest that your implementation is at fault. I have never seen a Form_Open that was given a "Cancel = -1" to ignore that attempt at cancellation.

However, be aware that you show that you used "Cancel=True" and there, you have a problem. "TRUE" is a valid SQL constant, but in VBA you have to use vbTrue or just use the explicit number -1 to have the desired effect. TRUE is not actually a valid VBA constant.

Some time ago I built a reusable general module that included some common public constants and the TRUE & FALSE constants were part of the list. That module happened to also contain some public color constants, which was its primary reason to exist, but I added some other useful constants and lists (via ENUM) and that resolved the constants issue.

 

[riktek]

I would have to suggest that your implementation is at fault. I have never seen a Form_Open that was given a "Cancel = -1" to ignore that attempt at cancellation.

However, be aware that you show that you used "Cancel=True" and there, you have a problem. "TRUE" is a valid SQL constant, but in VBA you have to use vbTrue or just use the explicit number -1 to have the desired effect. TRUE is not actually a valid VBA constant.

Some time ago I built a reusable general module that included some common public constants and the TRUE & FALSE constants were part of the list. That module happened to also contain some public color constants, which was its primary reason to exist, but I added some other useful constants and lists (via ENUM) and that resolved the constants issue.

Thanks. That makes sense. I recall some arcane Allen Browne code for clearing Form.Filter, where he used either ="(True)" or = "(False)". The quotes were to get the string into the property and the parentheses were for .Filter to evaluate the string for its value (and, to your point, the Form object clearly operates via SQL on a dynaset and .Filter is just its WHERE clause), so it works there.

Here, however, not so much. Cancel = True is what the Microsoft documentation recommends and that, and all the variations you and I discussed, failed identically (including "Cancel = -1", which you specifically endorsed). The procedure as it stands now (with results commented):

Private Sub Form_Open(Cancel As Integer)
    Debug.Print Me.Name & vbTab & "Form_Open()"
    'Cancel = True   '(MS documentation guidance)   Fails.
    'Cancel = (True) '(Attempt to force evaluation) Identically ineffective.
    'Cancel = -1     '(Numeric True evaluation)     Identically ineffective.
    'Cancel = vbTrue '(VBA constant)                Identically ineffective.
    Cancel = 1      '(Positive integer)             Identically ineffective.
    With Startup    'Startup is a predeclared class module.
        If Not .Main Then .SetMain
    End With
End Sub

All print the same text as described in my OP, in the Immediate pane.

So, unless you disagree, my implementation seems spot-on.

Any other thoughts?

 

[The_Doc_Man]

In the list of things you tried, I'm not surprised that Cancel = (True) did nothing, because all that would do is evaluate the expression inside the parentheses - which is True - then remove the parentheses to let the value "bubble up" through the parenthetical nesting. So it would in effect be identical to your previously listed attempt of Cancel = True (without parentheses).

Put a breakpoint on the IF statement that occurs after you attempt to set a value that would cancel the Form_Open event. Hover your cursor over the Cancel that appears in the argument list of the sub declaration line to see what value has been set.

OR put a debug.print of the value of Cancel as the last line before the End Sub. Like maybe on the line after End With.

Also, if you have Warnings disabled, enable them as a test. You want the form to issue a "Form Open was canceled" message.

 

[MarkK]

I have a form with no controls and only this code...

Private Sub Form_Close()
    Debug.Print "Close"
End Sub

Private Sub Form_Load()
    Debug.Print "Load"
End Sub

Private Sub Form_Open(Cancel As Integer)
    Debug.Print "Open 1"
    Cancel = True
    Debug.Print "Open 2"
End Sub

When I open the form by double clicking its name in the nav pane, I get this in the immediate pane...

Open 1
Open 2

... exactly as The_Doc_Man argues will occur. The Load event and the Close event do not fire.

But you have not shown us the code in the class Startup and you have not shown where it is declared, where the instance is created, or what code it contains. To adequately explore the effects you describe, we need to know more.

 

[riktek]

In the list of things you tried, I'm not surprised that Cancel = (True) did nothing, because all that would do is evaluate the expression inside the parentheses - which is True - then remove the parentheses to let the value "bubble up" through the parenthetical nesting. So it would in effect be identical to your previously listed attempt of Cancel = True (without parentheses).

Put a breakpoint on the IF statement that occurs after you attempt to set a value that would cancel the Form_Open event. Hover your cursor over the Cancel that appears in the argument list of the sub declaration line to see what value has been set.

OR put a debug.print of the value of Cancel as the last line before the End Sub. Like maybe on the line after End With.

Also, if you have Warnings disabled, enable them as a test. You want the form to issue a "Form Open was canceled" message.

Thanks.

I figured it out.

I noted just now that the behavior didn't manifest when just opening the form. I had been opening the form by launching the app.

The startup form calls procedures from the predeclared Startup class module, which instances it, triggering its Initialize event (this event does not occur until first reference; "predeclared" is not "pre-instanced", but that's another discussion). Its Class_Initialize() event procedure had a DoCmd.Close call for the startup form (now commented), as follows:

Private mblnMain As Boolean

Private Sub Class_Initialize()

    'DoCmd.Close acForm, "frmStartup", acSaveNo
    mblnMain = False
    DoCmd.OpenForm "frmMain", acNormal, , , , acHidden
    
End Sub

After commenting the DoCmd.Close call, the later events didn't occur on launching the app when Cancel = -1. Put otherwise, the DoCmd.Close call was triggering the later events to occur, presumably by forcing event progression notwithstanding having canceled the Open event.

I had been thinking the class module code was bare bones but hastily altering how I opened the form in testing various of your suggestions revealed its inconsistent behavior and the solution was obvious. Silly and obvious in retrospect, but thanks. Besides that, the True/-1 distinction will have legs.

FWIW, the warnings didn't come up at all, even when calling DoCmd.SetWarnings in the procedure.

 

[riktek]

I have a form with no controls and only this code...

Private Sub Form_Close()
    Debug.Print "Close"
End Sub

Private Sub Form_Load()
    Debug.Print "Load"
End Sub

Private Sub Form_Open(Cancel As Integer)
    Debug.Print "Open 1"
    Cancel = True
    Debug.Print "Open 2"
End Sub

When I open the form by double clicking its name in the nav pane, I get this in the immediate pane...

Open 1
Open 2

... exactly as The_Doc_Man argues will occur. The Load event and the Close event do not fire.

But you have not shown us the code in the class Startup and you have not shown where it is declared, where the instance is created, or what code it contains. To adequately explore the effects you describe, we need to know more.

Thanks, you're right that the solution was elsewhere. See my prior reponse for details including the errant code.

 

[June7]

However, be aware that you show that you used "Cancel=True" and there, you have a problem. "TRUE" is a valid SQL constant, but in VBA you have to use vbTrue or just use the explicit number -1 to have the desired effect. TRUE is not actually a valid VBA constant.

Doc, I am confused by this statement. Cancel = True is valid code in several event procedures.

 

[Gasman]

I am of the opinion that Cancel = True is correct?
I have used that in other events, not form open though.

As mentioned, this is what MS state https://learn.microsoft.com/en-us/office/vba/api/access.form.open

My understanding is that it will cancel the form open, but ONLY when it gets to the end of the Open event code?
So if you do not jump to an exit label, or Exit immediately after, the the rest of the code is run.

 

[Josef P.]

Note:
The Cancel parameter is only the option to return a value to the caller via the event.
This is not a method that leads to immediate termination.

Private Sub Form_Open(Cancel As Integer)

    Debug.Print Me.Name & vbTab & "Form_Open()"
    Cancel = True
    Exit sub ' => Exit the procedure and leave the cancel processing to the event trigger.

    With Startup 'Startup is a predeclared class module.
        If Not .Main Then .SetMain
    End With

End Sub

How Cancel works (as VBA dummy code):

Public Event Open(ByRef Cancel as Integer)

private Sub InternalProcedureThatTriggersTheEvent()

   Dim Cancel as Integer

   RaiseEvent Open(Cancel)
    ' ^
    ' |-- The event handler(s) can react at this point.
    '     The code is only continued when all procedures reacting to this event have been completed.

   If  Cancel = -1 then
       ' Cancel open process
       ...
       Exit Sub
   end if

   'Continue open process
   ...

end Sub

In principle, this is not much different from returning a value to a byref parameter of a procedure. There, the code also continues to run normally until the end.

private sub DoSomething()

   dim CalcValue as double

   if TryCalcSomething(1, 2, 3, CalcValue)
        ' do something with CalcValue
   end if

end Sub

private function TryCalcSomething(byval P1 as double, byval P2 as double, byval P3 as double, ByRef ReturnValue as double) as Boolean

     if P3 <> 0 then
         ReturnValue = P1*P2 / P3
         TryCalcSomething = True
     end if

     ' Any other code can be executed here.
     ' The code of the calling procedure only continues to run once this has been processed.

end function

 

[riktek]

In principle, this is not much different from returning a value to a byref parameter of a procedure. There, the code also continues to run normally until the end.

@Josef P. , this has been my understanding and experience regarding both the passback and the procedure, and thanks for pointing it out.

Among other cases, I have a few standard navigation routines that take a Cancel argument ByRef so as to cancel (conditionally) the calling event procedure, which is useful for surrendering focus. Both the called procedure and the calling event procedure continue processing; the effect of "Cancel = True" in those cases is to interrupt event progression after, but not during, those procedures. For the latter, something like an Exit statement would be necessary.

Here, I simply hadn't apprehended the effect of calling DoCmd.Close in the midst of things, which seems (loosely) to cancel the Cancel, as it were.

 

[The_Doc_Man]

To those who responded that TRUE is actually defined... It wasn't always defined. I have to assume that a more recent version of the Access libraries now includes that constant, which correctly translates to -1 if you use DEBUG.PRINT INT(TRUE). All I can say is that I don't remember that always being true. Unfortunately, I don't have a copy of something to test that on any more.

 

[Gasman]

Well all I can say is that it worked for me even when I was using 2007. Now on 2019.

 

[gemma-the-husky]

To those who responded that TRUE is actually defined... It wasn't always defined. I have to assume that a more recent version of the Access libraries now includes that constant, which correctly translates to -1 if you use DEBUG.PRINT INT(TRUE). All I can say is that I don't remember that always being true. Unfortunately, I don't have a copy of something to test that on any more.

Your observation made me think seriously about this. I used to think that cancel = true couldn't be correct for reasons similar to your own, but I've ended up using it anyway. Maybe any value other than zero works as a cancel.

 

[The_Doc_Man]

Your observation made me think seriously about this. I used to think that cancel = true couldn't be correct for reasons similar to your own, but I've ended up using it anyway. Maybe any value other than zero works as a cancel.

Thanks for the confirmation. As to "any value other than zero" - I think that is correct for VBA. We have to remember this: Boolean (as a VBA data type) or Yes/No (as a table-design data type) are both TypeCasts of BYTE (INTEGER). So the internal test of a Boolean/YN case would be to do an arithmetic comparison. Don't forget that the advice in the on-line help for the CANCEL=TRUE case says you can use 1 or -1 as well as TRUE. VBA won't complain about use of numbers because the Boolean data type "inherits" the integer nature of its base type. I don't know that I've tried it lately, but I recall that anything NON-ZERO will count as TRUE for the VBA things I've done in the past.

I do have to be careful about that though, because for some implementations of BASIC, only bit 0 counts. (Which means "EVEN" numbers are false in those cases.)

In the VBA language specs (4/24/2014) document, Boolean data types have enumerated values FALSE = 0 and TRUE = -1, and the issue when you supply a number other than 0 or -1 for a Boolean involves something called Let-coercion - a non-trivial process. Interestingly enough, the constants are defined as INTEGER, not as BYTE (INTEGER) - even though the variable or field is a byte.

 

[Pat Hartman]

As you have discovered, the Cancel isn't immediate so your code always runs to the natural end of the procedure unless you exit immediately after setting the Cancel argument.. When you write "Cancel = True", you are providing a value for the cancel argument of the procedure. Not all events have a Cancel argument and that means they cannot be cancelled.

If you tell us your objective, we can always provide a better option. But cancelling the Form's Open unconditionally just doesn't make any sense.