Solved Ugly new Access bug

[Pat Hartman]

However, you said this is the same code that has worked for a really long time. To be perfectly honest,

What I meant was that the End option of the error message always let you close the form. The fact that you cannot close the form gracefully after this particular error is the bug. I've never encountered this error before. This code to return to the calling form or the "menu" exists in most of my apps. The reason this form failed was because the app I copied it from had a different "main menu" and that form was not in the database and I didn't notice . So normally, opening the form not from the menu would simply result in loading the "menu" form when the form closed and so never generate an error.

The four options for the error message are Continue, End, Debug, and Help. In all other situations, End closes the form gracefully without having to terminate Access.

Adding an error trap to the Unload event allows the form to close but I still think this is a bug.

Private Sub Form_Unload(Cancel As Integer)
    On Error GoTo ErrProc
        If IsNull(Me.OpenArgs) Then
            DoCmd.OpenForm "Switchboard", , , , , acWindowNormal
        Else
            DoCmd.OpenForm Me.OpenArgs, , , , , acWindowNormal
            'SS_OpenForm Me.OpenArgs
        End If
ExitProc:
    Exit Sub
ErrProc:
    Exit Sub
End Sub

 

[isladogs]

OK, rather than go around the same points repeatedly, I've tested again in two older Access versions

A2007: form stays open as in later versions

A2003: form closes on clicking End.
That means the End action wasn't correctly applied as code was allowed to run despite the fact that it should have stopped executing.
So you may have 'got away with it' in A2003 but that really WAS a bug which was thankfully fixed in later versions.

As a final check I tried again with a .MDB file in A365. The form stayed open on clicking End . . . just as it should.

 

[Pat Hartman]

That means the End action wasn't correctly applied as code was allowed to run despite the fact that it should have stopped executing.
So you may have 'got away with it' in A2003 but that really WAS a bug which was thankfully fixed in later versions.

You've got it backwards. End is supposed to simply close the form. It isn't supposed to run any code from the form. Debug stops the code and opens the VBA window so you can fix the problem or at least see what caused it. End should stop the code and close the form. What if the fix has nothing to do with the code? What if the fix is adding in the missing form? Refusing to close the form and completely blocking the app from doing ANYTHING, isn't ever an answer.

As a final check I tried again with a .MDB file in A365. The form stayed open on clicking End . . . just as it should.

If you think that End should leave the form open, what do you think Continue should do? Seems logical to me that Continue leaves the form open, End closes the form, and Debug opens the debug window so you can see/fix the code that caused the problem.

There should NEVER be a situation where MS deliberately puts the db into an endless loop situation forcing the use of Task Manager to close the app. This is a bug. Don't report if you don't agree. I'll do it myself.

 

[Pat Hartman]

I added stuff to the sample and sent the error to MS.

Thanks for your help.

 

[isladogs]

You've got it backwards.

I disagree and, so far, I see nobody else supporting your viewpoint

Just to be clear, as I'm sure you are aware, the “End” button you're referring to is part of the standard VBA unhandled error dialog—not a VBA command being executed. When no structured error handling is present (On Error GoTo, etc.), Access (like other VBA hosts) displays a dialog with End and Debug options. Clicking End simply halts code execution at that point. It doesn’t invoke the End statement or any code logic—it’s just a UI option presented to the user when an error isn’t caught by structured error handling.
Continue is sometimes enabled depending on the situation - see post #13

AFAIK, Microsoft doesn’t document this dialog in detail, but their official error trapping guidance makes it clear that proper error handling prevents this dialog from appearing in the first place.

I understand we may see this differently, but this behavior is consistent across all VBA environments and has been for over 20 years. If you’ve seen documentation to the contrary, I’d be genuinely interested in reviewing it.

EDIT: I've just read the post above saying that you've reported this as an issue to the A-team.
I'll be interested to see their response if you've reported it in the non-NDA email group

 

[Pat Hartman]

I didn't report it to the NDA. There is no point. It needs to be looked at by someone who knows how the Continue, End, Debug dialog is intended to work. I don't need help fixing the problem. It isn't a problem that would ever happen in production since the necessary form would always be in the database. This only happened because I imported one form from a database that had a different "main menu" form and I didn't notice until I got the error.

Thanks again.

 

[isladogs]

OK hopefully you'll get a helpful response from Shane who will definitely know how each part of that dialog should behave.

 

[sonic8]

A2003: form closes on clicking End.

JFTR: In A2002 the form will also close when there is an End statement in the Unload event.

That means the End action wasn't correctly applied as code was allowed to run despite the fact that it should have stopped executing.

What? I'm afraid, you lost me there.
Which code was allowed to run?
Why wasn't End applied correctly if the form closes? - Any documentation link to support this opinion?

End is supposed to simply close the form.

Why is End supposed to close the form? - Any documentation link to support this opinion?

Both of you firmly insist that your own view is correct, without referring to any documentation supporting your opposing opinions.
To me @Pat Hartman's view is slightly more convincing, as End will reset all variables, including the Cancel argument, and with Cancel=False, I don't see a compelling reason why the form shouldn't close.

Just to be clear, as I'm sure you are aware, the “End” button you're referring to is part of the standard VBA unhandled error dialog—not a VBA command being executed.

What is the difference between the effects of the End button in that dialog and the End statement in VBA?

Clicking End simply halts code execution at that point. It doesn’t invoke the End statement or any code logic—it’s just a UI option presented to the user when an error isn’t caught by structured error handling.

The verb "to halt" is bit vague in this context. Do you mean it pauses the VBA code or it terminates the code?
Clicking "End" terminates all running VBA code. As does the End statement in VBA. - So, where is the difference?

 

[isladogs]

@sonic8
Whilst I think it better to wait for an official response, here are my initial thoughts on your post

JFTR: In A2002 the form will also close when there is an End statement in the Unload event.

In fact it also happens in A97 - I didn't check back any further

What? I'm afraid, you lost me there.
Which code was allowed to run?
Why wasn't End applied correctly if the form closes? - Any documentation link to support this opinion?

Agreed - not as clear as I had intended
I meant that in older versions up to 2003, the form was able to unload successfully despite the error and therefore close.
In newer versions (2007+) that doesn't happen

My understanding of the End statement in the error dialog is that it stops code execution immediately
Others have confirmed that viewpoint.

Further tests with e.g. a MsgBox after the If...Else..End If clause confirm that to be the case in ALL versions
I also tested setting a variable before the clause and running a Debug.Print before/after the clause.
In ALL versions, any code remaining after the error doesn't run

So why would you expect the unload to complete and therefore the form to close unless error handling is used?

I searched for official documentation early in this post but found nothing explicit
CoPilot (certainly not always reliable I know) confirmed my understanding and gave the error trapping link I provided.
I used a modified version of its response in a previous answer.

Just to clarify, the “End” button you’re referring to is part of the standard unhandled error dialog in VBA—not a command being executed. When Access encounters an error without structured error handling (On Error GoTo, etc.), it throws up that familiar dialog with End and Debug. Clicking End simply halts execution—no magic, no mystery, and definitely no End statement being run behind the scenes.

Microsoft doesn’t spell this out in neon, but their official error trapping guidance makes it clear that proper error handling avoids the dialog entirely. Which, frankly, is the best way to never have to explain what “End” means in the first place.

Both of you firmly insist that your own view is correct, without referring to any documentation supporting your opposing opinions.
To me @Pat Hartman's view is slightly more convincing, as End will reset all variables, including the Cancel argument, and with Cancel=False, I don't see a compelling reason why the form shouldn't close.

Nor do you provide any documentation to support that statement. I'm not disagreeing with it . . . just making the point.

The verb "to halt" is bit vague in this context. Do you mean it pauses the VBA code or it terminates the code?
Clicking "End" terminates all running VBA code. As does the End statement in VBA. - So, where is the difference?

Trains halt at the terminus. Code halts/terminates when it Ends

 

[The_Doc_Man]

I remember a long time ago that the system "Unhandled Exception" offered a "Reset" button rather than the "End" button, but I haven't seen that one lately. I am wondering if the modern UE message box "End" is the same as the old "Reset". (It behaves that way, certainly.)

In any case, the question came up regarding the difference between the End button in the Unhandled Exception message box and the End statement in VBA.

To the best of my understanding, and depending on the "Trap on unhandled exception" Option from the File >> Options settings, the Unhandled Exception box will let you see the values in a code module before all run-time contexts are released / dissolved / erased. There were many discussions in this site in the Modules section at least 10 years ago (when I was still a bit raw on some subjects.) My question at that time was why after you got the dreaded UE message box that contained the "Reset" option, user context was lost. The answer was, of course, that all class modules related to the error would be dissolved and all variables in General Modules would be reset to their default initial values. Which is why error handling is advisable if you want to be able to recover from errors.

You have to remember that error handling in a module preserves the states of that module's variables. Not having error handling exposes you to the dreaded "Stack Unwind" operation. The unhandled error gets re-signaled to the caller of the faulting routine. But one other thing happens. In ANY module, all local variables (of the Private variety) reside on the stack inside of something called a Stack Frame. When an unhandled error occurs, that frame and ALL of its local "baggage" goes away. Ceases to exist. Forever out of scope.

When you get far enough up the stack, you remember that all EVENT code entry points are ALSO declared as a SUB that is called by MSACCESS.EXE as the MAIN program. When that Stack Unwind happens at the top of your event code chain, you are no longer in your Event code. You are now in Access internal code and your top-level event routine's local private variables are gone, too.

This is at least partly a guess, but what probably happens is that the DEBUG button stands between your current program state and the final dissolution of all context being invalidated by the fatal condition of the unhandled error. The END button just says "OK, go ahead and end it all." The DEBUG button lets you peek around at the ghost of what was. But eventually you have to give up the ghost.

The isolated "END" statement in VBA is NOT QUITE the same. Pretty darned close, though. It is an executable statement.

Terminates execution immediately. Never required by itself but may be placed anywhere in a procedure to end code execution, close files opened with the Open statement, and to clear variables.​

That "clear variables" part means that all contexts containing variables will reset them. Closing files that were opened with an OPEN statement is also pretty obvious. What is not so clear is whether files opened by implication are closed. The latter case includes files opened through an FE file because they are related to a recordset pointing into a separate BE file (probably on a server), which would require a file handle. I'm not saying such files aren't closed, but rather than it is unclear from the online help I've found so far as to whether they are closed. I'm GUESSING they are.

 

[Josef P.]

I wouldn't call it a bug in the sense of a coding error.
But if we define a bug as undesired behavior, then I agree.

At the very least, when switching to design view, I would expect the form not to remain stuck in form view if an unhandled runtime error occurs.
I would expect a message similar to what we get when switching to design mode in the VBA editor.

=> Click on OK => form is open in design view.

 

[sonic8]

My understanding of the End statement in the error dialog is that it stops code execution immediately

I fully agree with this.

So why would you expect the unload to complete and therefore the form to close unless error handling is used?

First, we must consider that we are talking about the Unload event here. Access is telling us: "I'm will unload this form immediately. If you want to run some code before that, do it now!" The code we might put into the Unload event procedure is not really part of the unloading process. It is just ran before unloading commences.

Second, the term "complete" can also be interpreted differently from how you do it.
Access invoked the Unload event. Execution of the VBA code was terminated and thus is completed. The Cancel argument was False after control returned to Access itself. No reason to not close the form.
"End", regardless whether the button or the statement, will only terminate VBA code execution. The native Access code continues to run. It must be shielded from errors and code termination in the VBA code, otherwise Access would crash. So, it is reasonable (not necessarily correct) to assume that Access will only care about the value of the Cancel argument after the Unload event code completed (as in "is no longer running").

I searched for official documentation early in this post but found nothing explicit
CoPilot (certainly not always reliable I know) confirmed [...]

AIs are great at summarizing existing information, which is by and large consistent.
However, if there is contradicting information or no information at all, their results are highly questionable.
Quod erat demonstrandum...

Clicking End simply halts execution—no magic, no mystery, and definitely no End statement being run behind the scenes.

If there is neither the code of the End statement nor magic involved, how does clicking the End button "simply halt execution"? - I would have guessed that the internal code linked to the VBA End statement must be the most suitable way to "simply halt execution" of VBA code because that is what it was designed for.

Nor do you provide any documentation to support that statement. I'm not disagreeing with it . . . just making the point.

Your point is weak. I assumed you and other gentle readers would either know the documented Cancel argument or are able to find it quickly in the documentation of the Unload event, which is the topic we are debating here.

 

[NauticalGent]

I've been following this and I sure as hell cant offer anything of substance that has already been said. Phil, your last post is very intriguing, thanks for your insight.

Out of curiosity, would you classify this as a bug and if not, what WOULD you define this behavior?

 

[isladogs]

Agree that Philipp's contribution was very valuable. Its critical tone certainly made me question some of my earlier comments.

Unfortunately, Microsoft do not appear to have any documentation for exactly what End should do in that error dialog
I had searched for it unsuccessfully.
Nor is it mentioned in various form events documentation that I had looked at including Form_Unload

Lets try and see what we all agree on:

1. Clicking End in the error dialog stops code execution immediately
2. Up to A2003, Pat's code in Form_Unload closed the form after triggering the error
3. From A2007 onwards, the error triggers but the form doesn't close.
4. Using error handling avoids any issues
5. The behaviour is identical for ACCDB and MDB so its (probably) not changed as a requirement of the new file format

I've done some further tests using exactly the same code in different events.
In each case the error occurs and no code after that runs in that event

• Open event (cancellable) - the form does not open
• Load event - the forms finishes loading and proceeds to the next event (Activate)
• Activate event - completes and moves to next event (Current)
• Current event - completes and moves to next event (Timer)

and then

• Unload (cancellable)- does not proceed to next event (Deactivate)
• Deactivate - completes and moves to next event (Close) or closes if no further event code
• Close - completes and form closes

As the only events that fail to 'complete' are those that can be cancelled, this may be the clue to the behaviour.
But the code supplied by @Pat Hartman is not trying to Cancel the event

All this still doesn't confirm whether the changed behavior from almost 20 years ago was deliberate or a bug.
I stated my opinion back in post #10 but maybe I am wrong. Certainly, I wish I had stopped after post #10!

However, to repeat, I had already checked the documentation for Form_Open & Form_Unload and it does not provide clear information on this behaviour. The only warning is this paragraph in the Form_Unload Event page

Note

When you create macros or event procedures for events related to the Unload event, such as Deactivate and LostFocus, be sure that they don't conflict (for example, make sure that you don't cause something to happen in one macro or procedure that is canceled in another) and that they don't cause cascading events.

Either way, the behaviour is certainly undesirable (as Josef P. said above) and re-emphasises the need for error handling

I would have no personal objection if the original behaviour was restored. It has never bitten me in all the years I've been using Access.
I doubt very much backward compatibility would be cited as an issue as I suspect this has hardly ever arisen in almost 20 years.

In her sample db to the A-team Pat has asked whether this behaviour is intentional:

The database contains two forms. One with an error trap is suffixed _AllowClose, the other is suffixed _CannotClose.

I think in the _CannotClose version, the End button on the Access error message should gracefully close the form. Instead, it leaves the form open, which is what I think Contunue should do, except that Continue is greyed out. Because End does not close the form, the user is forced to use Task Manager to close Access.

If you disagree regarding the operation of the End button, then please explain the difference between Continue and End.

Hopefully Shane from the A-team will clarify the behaviour including the purpose of each button and we can all move on!

 

[Pat Hartman]

Why is End supposed to close the form? - Any documentation link to support this opinion?

Because "Continue" is an option and that seems to imply that the form stay open AND trying to close the form raises the error because it causes the failing code to run? Without the Continue option, End would be ambiguous.

All this still doesn't confirm whether the changed behavior from almost 20 years ago was deliberate or a bug.
I stated my opinion back in post #10 but maybe I am wrong. Certainly, I wish I had stopped after post #10!

Thanks for all the research. I don't have old versions of Access loaded and so could not determine when the behavior changed. Granted, this is an unusual error so I probably never encountered this situation after converting to A2007. It would probably only occur in an event involved in form shutdown because the error would have to occur in one of those events to effectively stop a form from closing. PERIOD.

The code I wrote does not include an error trap because I don't include them in procedures that are unlikely to fail. In this code, the If statement should have prevented any error from occurring. The error only occurred because I imported a form from a database that had a different "main menu" form from the one it got imported into. And the error could never survive even the most basic of testing. It failed the first time I tried to close the form. I immediately fixed the code to specify the correct "main menu" form. So there was never a question of how to resolve the problem The question has always been --- is this a bug?

Many things were changed with A2007 and some I have never forgiven the children for changing, but we won't go there now.

The bottom line is - the situation as it currently exists effectively prevents the programmer (this error is not likely to survive testing) from closing the form unless he is willing to comment out the failing code. The alternative is using Task Manager to shut Access down which always terrifies me when I have to do it because it feels like it could easily lead to corruption depending on what is keeping Access from closing the form normally.

Since Colin was able to determine that this is a behavior change, then was the change deliberate or accidental? I'm sure we'll never know since the change happened so long ago unless the programmer who made the change made a comment in the code as to why the behavior was changed. We have no way of knowing if or how many complaints to MS this change has caused. Probably not enough to warrant fixing if the fix isn't simple.

 

[Pat Hartman]

Either way, the behaviour is certainly undesirable (as Josef P. said above) and re-emphasises the need for error handling

What this means is that THIS particular event ALWAYS needs error handling, just in case. Because it is this particular event that is not allowing the form to close so that the error could be fixed if the fix didn't require a code change. The If statement should have prevented any potential error although it didn't account for programmer error. In my case, the fix was to substitute the correct form name for the incorrect one. But the fix might easily have been to import the form that was being referenced and that couldn't be done without closing the recalcitrant form. So what MS effectively did was to force an unnecessary code change in order to avoid using Task Manager to close Access. This smells like a bug to me. But it probably isn't important enough for MS to actually fix their code to allow the form to close.