Can I Return From The Current Event Stack In Micro

Question

I would like to know if there exists a pre-built system function that would cancel the whole memory stack of the current event being fired, without having to exit every procedure in the stack trace, in Microsoft Access VBA?

I am looking for something like the following:

Public Sub Procedure2()
    CancelEvent 'This would return from procedure2, proedure1, and the specific event Sub
End Sub

Public Sub Procedure1()
    Procedure2
End Sub

Private Sub SomeControl_SomeEvent()
    Procedure1
End Sub

I have tried DoCmd.CancelEvent, but program execution still continues afterwards, and do not cancel the whole procedure stack (I may have been looking for the wrong system Sub). The only way I can think of to achieve this thing (without having nasty If-Else statements to check for if the below stack procedures must continue if I exit the above stack procedure) is to use exceptions, and have something like the following:

Public Sub Procedure2()
    Err.Raise 1  'Just an example exception
End Sub

Public Sub Procedure1()
    Procedure2
End Sub

Private Sub SomeControl_SomeEvent()
    On Error Goto HandleError
    Goto StartSub
HandleError:
    Exit Sub
StartSub:
    Procedure1
End Sub

Alternatively, would it be possible to catch specific exceptions only in VBA, as I may have to catch this exception ONLY at the lowest stack procedure?

Answer 1

That has to be implemented as your error-handling strategy.

I'd define a public enum to formally track (and name) custom error codes, starting at vbObjectError + some value:

Public Enum CustomError
    CE_Cancelled = vbObjectError + 42
    CE_SomeOtherCustomError
    '...
End Enum

Then when you want to "cancel" an event and "walk up the call stack", you can raise that error:

Public Sub RaiseOperationCancelledError(ByVal source As String)
    Err.Raise CE_Cancelled, source, "Operation was cancelled."
End Sub

Exactly what will happen when RaiseOperationCancelledError runs, depends entirely on what On Error statements you have in the call stack at that point.

Say you have some control event at the top (erm, bottom actually) of the call stack:

Private Sub SomeControl_SomeEvent()
    On Error Goto CleanFail
    DoSomething
CleanExit:
    Exit Sub
CleanFail:
    If Err.Number = CE_Cancelled Then
        MsgBox Err.Description '"Operation was cancelled."
    Else
        Err.Raise Err.Number 'we don't know what happened; rethrow.
    End If
    Resume CleanExit
End Sub

If DoSomething does, IDK, say file I/O, and needs to handle errors:

Sub DoSomething()
    On Error GoTo CleanFail
    Dim fileNumber As Long
    fileNumber = FreeFile
    'do stuff
    '...
CleanExit:
    Close fileNumber
    Exit Sub
CleanFail:
    If Err.Number = 53 Then
        ' handle "file not found" error
        Resume CleanExit
    Else If Err.Number = CE_Cancelled Then
        Close fileNumber ' we won't run CleanExit if we rethrow!
        Err.Raise Err.Number ' rethrow
    End If
End Sub

Now, that's pretty much as sloppy as it gets. So what's the problem?

The problem is that you're using custom run-time errors for flow control, and that inevitably turns into spaghetti code in any language, with or without exceptions.

If you were in the middle of doing something and the user cancelled it, then you would have a function that returns a value that tells you exactly that - e.g. the user cancelled the file browser dialog, we can't continue the export - you don't raise a custom error! Instead you make the function whose responsibility is to return the user-selected file, use some mechanism to tell its caller that there's not going to be an export target after all - the function might return an empty string instead of a valid file path; the caller then knows that if the returned string is empty, it needs to bail out and return to its own caller, unwinding the call stack naturally.