Variables corrupted when closing all instances of a form, debugger problems too

[twgonder]

@Edgar, that worked to fix the rt err, and I was wondering why you did that large string.


Part of the reason I created my demo was to see how to work around this problem as mentioned by Allen Browne:
(Remember to force a save before closing if Dirty: a bug in Close silently discards your edits if there is any reason why the record cannot be saved, such as a required field missing.)

Sr. Allen missed that code isn't running in the forms with his sample db, or it's a bug introduced since Access 97.

I didn't want to have a save EVER if it was an automatic shut-down initiated by an administrator, as I didn't want unvalidated records saved because Access doesn't see a problem with its validations.

Try adding this Stop here in form2, and then close three open forms with the Close All button. Did each form do the Stop? If not, then (other necessary) code for the form isn't executing (which would update a table, which you don't have).

Private Sub Form_Close()
Stop 'Does this run for each form?
    RemoveOneFromCollection Me.hwnd
    
    ' Populate listbox
    CheckCollection
End Sub

I stated in posts #1 & 11 that this is a problem with using .remove to close forms.

[twgonder]

I have my doubts that
Docmd.close acForm, "Form2_2" (which is what Me.Name will evaluate as)

knows which "Form2_2" form it is supposed to be closing when there are more than 1 of them open.

I can tell you, that Access surely doesn't. Which means there is a big oversight since Access 97 on how to close an instance, as I haven't yet found any documentation dealing with this other than to use .remove. But worse is the problem with code in the form not running during a close and contamination of variables that results from closing in either of the two ways I've experimented with.

With the caveat that I'm still looking at Edgar's approach.

[Edgar]

Try adding this Stop here in form2, and then close three open forms with the Close All button. Did each form do the Stop? If not, then (other necessary) code for the form isn't executing (which would update a table, which you don't have).

I added the Stop you mentioned. When I click "Close All", it goes to the Stop only once and only executes the code of the earliest opened form. But take a look at my database, I added some code that executes when any instance of form2 closes, it's a debug print and an insert to a table. On my computer, it executes every single time regardless of closing the form manually, via the listbox or via the Close All button. It only "fails" when I add a Stop to the on close event, in which case, it behaves as mentioned. And in my book, that makes sense, because the debugger does not trigger user events.

Basically, what I'm seeing is not a bug, it is something expected (so far). If I step through the code, it does not fire events. But if I interact with the form, the events work perfectly.

Check the database attached

[twgonder]

@ Edgar I'm calling it a day. I'll check out your db tomorrow. If you are still using the .remove to close the forms, then you might still be suseptible to the bug Allen Browne pointed out.

I chose the route to close the form from within, so that I know that it should close despite any .remove bugs and that all my error checking code or undo would run without being hidden to the debugger, or worse, not running at all.
I made some changes along a similar vein in my code to get rid of the stops, message box and some of the break points I sometimes tested.
That seemed to take care of the variable corruption, but it's still not quite what I would expect. If the instance is a copy of the form, I would think the debugger should be able to deal with that. Again, I suspect the MS software "engineers" went down their own path when implementing "spawning" without taking a little time to understand how it's worked reliably for 30 years in other environments before they did their implementation. How many years ago was Access 97 released?

I can get my demo to sometimes close all the forms and execute some of the code, but not always the docmd.close in Form_Timer.
To test it, it's quite simple, load MyForm2_2 from the Main Menu, wait a few seconds and click the option button on the Main Menu.
Now turn off the option button and start two forms, etc. (keep adding more forms in each test)
You can check the immediate window to see some indication of what's running.
If the detail section of the form goes red, you know the code started and for which form.
Once I get past 3 or 4 forms, they all don't close.
I've tried all kinds of things, but the code keeps failing to run completely, and it makes no sense to me.
If I can't get it to run in the stripped-down version, how will it run in the big form?

You can close all the forms with the X on the control box, so you know nothing's wrong with the code/data.
When the forms don't close, the VBA editor doesn't show a rt error at any point.

Please let me know what happens with your version of Access when doing the test. Did you try the crash test? Thanks

[Edgar]

I can get my demo to sometimes close all the forms and execute some of the code, but not always the docmd.close in Form_Timer.

There are Stop statements in that code.

If the detail section of the form goes red, you know the code started and for which form.

The color change happens immediately, I can't spot it.

Once I get past 3 or 4 forms, they all don't close.

To me it always closes the forms. But sometimes, if I click on the last opened form, it won't close it.

I've tried all kinds of things, but the code keeps failing to run completely, and it makes no sense to me.
If I can't get it to run in the stripped-down version, how will it run in the big form?

You can close all the forms with the X on the control box, so you know nothing's wrong with the code/data.
When the forms don't close, the VBA editor doesn't show a rt error at any point.

There are too many things going on in your code, that's why I proposed my sample database. Make it work there first and then implement in your app. I'm really not a fan of code only the developer understands, because this happens. I'm really trying to help, but it is too time consuming trying to understand what you meant with your code, I can see the "what" and the "how", but I can't understand the "why" or the "when". For instance, what on earth is adefSql or gSp(10)? (not an actual question, it's just to point out I have no idea why these are there, let alone why 10).

Please let me know what happens with your version of Access when doing the test. Did you try the crash test? Thanks

The crash test didn't crash the app, but it did close the form. I didn't check why it was doing that, but it should not have done so. I believe a lot of your problems come from the use of Stop. I don't use TempVars either, so I can't tell you whether they're good or not, but I see you depend a lot on them. Maybe check those TempVars, you might be storing something that isn't what you think.

[twgonder]

@ Edgar Back to this this morning. Good day. When I ran the Close2.accdb (and I'm doing it right now to be sure, as I described how to do the test) I don't encounter a single programmatic Stop. However, the few forms don't close, and the VBA editor isn't sitting at a Stop during inspection.

Can you please tell me where the Stop you're encountering is and how you got there please?

Yes, sometimes code gets complicated, and we don't always know what the original developer was thinking. It's not all that important here (although I've described what in in my posts, the why and there are many comments I've put in the code). There's nothing very strange in the code, to me anyways, it's just 40 years of experience being used to wrestle Access into being a useful tool. Ask about any specific code and I can clarify if you think it's important to the immediate problem. I don't want you wasting time. It's just routines calling routines.

Unless I'm doing something outside of Access defined commands, and the only thing there is the sleep routine, then no matter what the code does, it should work if it's using standard commands. The problem here is that the statements aren't doing what they are supposed to, like close instanced forms and without contaminating (or resetting) variables.

Thanks for the testing, so I can make a note of the results in different environments may I ask which version of Access and Windows you're using?

Now, I'm going to try and replicate the problem in your accdb.

[twgonder]

I don't use TempVars either, so I can't tell you whether they're good or not, but I see you depend a lot on them. Maybe check those TempVars, you might be storing something that isn't what you think.

The "stripped" down version is taken from a testing template that then get's incorporated in the big app. So what you are seeing, it's two levels removed and much simplified. Going too much simpler defeats the purpose, again for testing.

In the big app version, it's simply the Access Form calling its class VBA code which then calls my Form VBA (used by all forms for back-end handling like validation error checking, record locking, controlling command buttons, etc. that I don't want in every form). My Form VBA (not the Access VBA) then can call other procedures that aren't specific to a form. Pretty standard stuff in RAD design and structured programming. Nothing new here since subroutines were invented, what 80 years ago?

So, I use a lot of arrays to pass between procedures and modules. For system wide variables, I started with globals, but those kept getting reset during testing, requiring a complete restart of the .accdb. Someone here told me to use tempvars for their permanent nature. It was a good suggestion and they've worked without fail. What's not working are general variables and passed arrays when using an instance of a form.

One way to see this is, make these simple change to Close2, MyForm2_2, Private Sub Form_Close():

  If Valdt(0, 1) <> FrmName Or TestVariable = "" Then
    Debug.Print "." & Valdt(0, 1) & "."
    Debug.Print Me.Hwnd, "." & TestVariable & "."
  End If

(add the or and variable, and uncomment two lines)

Then run MyForm2_2 from the menu five times (and leave open), Close the Main menu, restart the Main Menu, then go to the Immediate Window.
If you see "..", then some variables were cleared out when closing MyForm2_2 and this is VERY BAD, and I can't think of any reason for this to purposefully happen.

After reviewing your .accdb, I'll see if it happens there too.

[twgonder]

Reply to post #19[

QUOTE=Edgar;508165]...

Basically, what I'm seeing is not a bug, it is something expected (so far). If I step through the code, it does not fire events. But if I interact with the form, the events work perfectly.

Check the database attached[/QUOTE]

Okay, I made a minor change to your database. Added a simple variable to test, and your database presents with the same problem as my big one.
Here's all the code for Form2 with changes in bold:

Option Compare Database
Option Explicit
Dim TestVariable As Variant


Private Sub Form_Load()
  TestVariable = Me.hwnd
  Debug.Print "Loading form variable", TestVariable
End Sub


Private Sub btnCheckVariable_Click()
    MsgBox Me.hwnd
End Sub


Private Sub Form_Close()
'Stop
    RemoveOneFromCollection Me.hwnd
    
    Debug.Print "closing " & Me.hwnd
    
    ' Insert some stuff
    InsertIntoTable Me.hwnd
    
    ' Populate listbox
    CheckCollection
    If TestVariable <> Me.hwnd Then Debug.Print "Bad variable!", "." & TestVariable & ".", Me.hwnd
End Sub

The TestVariable is getting reset before the form closes, even without a lot of "extra complicated code" that mine has.
Here are the immediate window results from running Form2 once from Form 1 (highlighted on green) and then a second time from the navigation pane (highlighted in orange).



See how the second run doesn't show a variable clearing error?

Or do you expect two different ways of running the same form to present with different results when closing? I sure don't. That's a big fat bug to me and a two week pain-in-the-arse (just one of the dozens I've had with Access). But maybe it doesn't exist in your version. I am using Access 2021 and it's there clear as day. So, after reviewing your smaller demo, we've confirmed exactly what I said in post #1.

That's why I went with the second approach in my demo db. It has its own inconsistent bugs in Access.

Cheers

[twgonder]

To wrap this up, I think we've come up with a simple .accdb, thanks to Edgar, that demonstrates 1/2 of the problem with closing instances.
My preferred method is to close from within the form, rather than without (using .remove) to control what's running and not. But as we've seen, the debugger isn't much help in this with "spawned" forms.

I'll probably modify Edgar's form to duplicated closing the form from within, so it's all in one tight, little .accdb. I'll post it here if I do.

1) I gave up on the normal channels of Microsoft support years ago when the phone support staff, and then an army of MCSE and various certificated MS professionals couldn't even get Exchange Server to backup and restore. The first test I do of any new product. Does anyone know how to get problems like these into the MS Access Bug Team for review, rather than having them sit on the first-tier shelf for decades?

2) Unfortunately, VBA is fairly new to me, and I see a lot of common practices that I would like to understand better. I see lots of code like Edgar's that does something like this:

Sub RemoveAllFromCollection()
    Dim i As Long
    For i = 1 To formCollection.Count
        formCollection.Remove 1
    Next
End Sub

a) I see programmers use very long names for their variables and procedures, and then resort to a coding convention from the 1963 for controlling iterations (using "i"). Is there a reason in VBA to use Long? I doubt we'll have billions of forms open. I would think a Byte declaration would be sufficient seeing that even any modern computer with tons of memory would probably still run out at 255 open forms, even if the programmer was dumb enough to allow that many forms open at a time.

b) I was taught to store the end value for a looping iteration in a variable because it will be more efficient that calculating when the loop should terminate in each iteration. Is this not the case in VBA too?

I know Edgar threw this together in a hurry to do a simple test, but that he would do this even in a simple example makes me question how VBA might be different, in a way that I don't yet understand. Heck, even Allen Browne did half of this in his example (is Kt short for "count"? If so, no one should be complaining about my naming conventions):

Dim lngI As Long

    lngKt = clnClient.Count
    For lngI = 1 To lngKt
        clnClient.Remove 1
    Next

[twgonder]

I made some changes to Edgar's .accdb posted earlier, to do more of what I was trying to do in my sample .accdb.

It fails in both ways. The problem seems to get worse the more times it runs, which may indicate some kind of memory or cleanup problem in Access.

I've gotten this .accdb to fail with as few as 4 forms opened.
Since we aren't using stops or the debugger,
watching what happens in the immediate window is key to understanding.

You can open several forms, and then try closing them all, by either form1's command or option button.
I've found the error to happen with more regularity if you wait a second or two between each click of the Open form button on form1 for the option button close failure.
The Close All test fails every time I've run it.
Basically, if you are seeing ".." in the immediate window (Close All error), or form2 remaining open in the Access window (Option button error), then you are seeing a problem with using instances.

You may have to run the test several times before it fails, it only takes a few seconds for each test.
If it does or doesn't work for you, I would appreciate a short note here saying which and what version of Access and Windows you are using.

Thanks for your patience.

[moke123]

I opened 20 instances and closed them all at once and seperately with no problem.

I used a dictionary rather than a collection.

[twgonder]

I opened 20 instances and closed them all at once and seperately with no problem.

I used a dictionary rather than a collection.

May I ask how many trials you did with the Database4-2.accdb and if it always worked doing what was explained?

I've been testing for over an hour, this is what the option button test looks like, even after ten minutes of sitting there:



Five out of fifteen failed to close.

[moke123]

May I ask how many trials you did with the Database4-2.accdb and if it always worked doing what was explained?

None. Your code never makes sense to me so I wrote my own. 0 out of 20 failed for me.

[twgonder]

None. Your code never makes sense to me so I wrote my own. 0 out of 20 failed for me.

It wasn't my code (if you were referring to post #25). Besides, you don't have to "understand" it to test it. Do you understand all the code behind Word or Excel?
But I'll check yours and add the problem if it's not there.

P.S. Of course yours "works". You didn't add anything to test for the two types of problems described.

The original by Allen Browne "works", as long as you don't really test it in something that resembles a real-world application.
Is your .accdb entering data? Is it validating the data at entry and save? Are you stopping a save when data isn't validating across fields? What real-world situation is causing your forms to close? Do you need to close down forms that weren't instanced? What has to happen if the form doesn't close for some reason? When slamming the forms down, do you want to save or discard partially modified records? Should code execute (like record locking, foreign record updates, audit trails, etc.) before the instance of a form closes? Do you use the Access model of implicit saves or are you more precise requiring an explicit save? Do you use the primitive autonumber IDs of Access or do you have a more WAN ready model for IDs? Do you repeat the same code over and over again for form basics or do you have a supporting module library for all forms to use? I don't mean a few fancy subroutines branded as classes. I mean a real library of 40 years' experience to handle foreign languages, name, address and phone formatting, and currencies for many countries, legal and business rules, just for a start. Do other table records need to revert back to a prior state should the form close unexpectedly? Etc. Those are some of the "old school things" in my code (hooks in post #19) that don't make any sense to you.

Ignoring all that, check out how I modified Edgar's (post #25) to replicate the two simple problems with instance closing. It's pretty similar to yours before I did that.

[Edgar]

None. Your code never makes sense to me so I wrote my own. 0 out of 20 failed for me.

Your dictionary implementation also failed to keep the variable. By the way, you also forgot to add code to remove items from the listbox when a user interacts with the built-in close button.FormInstance.accdb

Edit:
And I also forgot to say that only when the user interacts with the form, the test variable is kept.