This problem is somewhat a corollary of the problem in Thread :
https://www.accessforums.net/showthread.php?t=87614
I’m having a problem with two different forms calling the same module, when the module has a module variable. It seems that one form is stomping on the module variable of another form. As it works for me, this makes module declared variables useless and untrustworthy.
Some have claimed I don’t understand global and module variables (despite using them just fine in db programs other than Access for thirty years). So, I'm happy to learn the Access super-progressive-jet-aircraft method ,compared to my old-sailing-ship db.
In other systems, this kind of programming never gave me a problem, because if a procedure called a subroutine (old school name), then the module variables were kept in a separate work-space for each calling procedure. Quite common when spawning tasks (thread mentioned earlier) or calling procedures. This, I know, is different than a global, which doesn’t do that, as all modules/procedures share it.
This little demo shows some incest between forms that call the same procedure, and use a module variable to store some control information for later use in each form.
In Access, is it documented that module variables are shared between calling procedures? Does this make sense? If so, what is the solution to not having contaminated module variables between forms?
The only solution I see is to pass the variable back-and-forth between the procedures, but since I have dozens of these control variables being set in formload, the overhead for all this is a bit cumbersome. That’s supposed to be the job of the programming language (at least well written ones). Then, passing back and forth possibly becomes a problem with multiple instances of one form contaminating another instance, as we saw in the thread mentioned at the top (#87614).
Or, maybe there is some trick in Access I just haven’t found yet to avoid this kind of problem?
To test this in the attached demo db:
1. Open Form1, move forward and back a record. Look at the results.
2. Open Form2 move forward and back a record. Look at the results.
3. Go back to Form1 and move to the second record
Barney should not think he is a child.
If the argument is that one shouldn’t run more than one form at a time (which is pure hogwash in a Windows environment), then you can see the same behavior in the parent/child forms:
1. Open FormP
2. Move the parent portion of the form forward one record.
Bamm-Bamm shouldn’t think he is an adult.
Related to some of my previous posts, if you work in the VBA editor some, on this simple .accdb, you might get Accesss to crash, as it did for me about five times while writing it (Using Access 2021).