First, you must understand how things are allocated in memory. Note that because Access is NOT an OpenSource product, we cannot be totally sure, but over the years I've read some articles. This is more or less what happens.
Remember that MSACCESS.EXE is the MAIN program segment. You didn't write it. It came into being because of something Microsoft did to compile and link-edit the pieces-parts into a unified whole. But if Access is already whole, how do you get things to persist in its memory? Well, you do... but technically you don't; you can't. MSACCESS.EXE is a closed and compiled task image. You can't put anything IN it - but you can have it put things where it can see them. You can get things to persist in structures built in the Access Heap and Stack - two parts of ANY Windows program's working memory space. Your modules interact with the Access GUI environment to define things on your behalf.
When you activate a "standard" code or class module, the declaration area of that module gets stuffed somewhere in memory, probably on the Heap, as a "fully defined" structure with variables that happen to match the declarations you made at the top of that general module. We don't know exactly where it goes, but somewhere. You activate that module by calling or referencing something inside it, which causes it to become instantiated. That is, simply, that General Declaration Area memory gets allocated and filled at run-time. When you call a VBA routine (sub OR function) of ANY kind, the per-routine (private) declarations go into what is called a Call Frame on the Stack. The public variables (if any) can also go into the heap.
When you encounter any program error, you have a chance to intercept it, but if you don't successfully do so, the code that catches it is sometimes going to eat your socks. Why, you ask, does this happen? How is this important? It is of supreme importance because of what traps do to the stack. This next part is the reason that things get reset.
When you declare an error handler (ON ERROR GOTO CATCH_THE_WABBIT) and then an error gets handled, you have very few choices. IF your trap handler can do so, it will remedy the error and do a RESUME NEXT or a RESUME I_FIXED_IT, in which case you remain in the same context. BUT if you cannot fix the error, you have to let the routine do an EXIT SUB or a RESIGNAL. In so doing, you implicitly or explicitly drop the current call frame from the stack along with every variable that had been declared and placed in the call frame. In other words, after unfixable errors, you de-allocate the memory that HELD those variables. So your variable and object pointers now point to unallocated memory. Which is pretty much the definition of "Nothing." (OK, technically, they point to address 0, which IS unallocatable memory.)
IF your error handler isn't even written in the currently routine, Access must traverse up the stack to find a prior call frame with a defined but currently inactive error handler. That means you could end up removing TWO routines - and their variables - from the stack by collapsing the call frames. (You can look up the term "Unwind the stack" for a fuller definition.) You can unwind the stack as far as needed as long as you are still working on your own routines. But what happens if you don't HAVE a trap handler?
Have I mentioned the "Last Chance" error handler? No? If your trap gets handled there, you have traversed ALL of your application's stack and have stepped into MSACCESS.EXE stack. Remember that I said things get de-allocated from the stack on an Unwind operation? If you leave your application's stack behind, EVERYTHING becomes "nothing" and that is NOT a good thing.
IF you defined something in a class module (a form class module or a custom "object" class), remember that the code for THAT module only exists until the last (or only) user of that module withdraws from it - by doing an Exit Sub, for example. Again, when the last example of that class object goes away, the declarations and code of the object ALSO go away. When you close a form, the memory associated with that form goes away and the variables become nothing. See the trend here?
The final nail in this coffin is if you ever get to the point that you execute a RESET then just about everything becomes de-allocated. Some of our members have found a few structures that persist a reset operation. By the way, if you ever see a message box that gives you the choice of "DEBUG" or "RESET" ... you have just hit the Last Chance handler. It would be rare to see the "RESUME" option not blanked out for that box, because most of the time your situation will not ALLOW that option.
Which leaves us with the general module declaration area and any public variables therein. The RESET operation usually leaves you with ALL memory structures having been de-allocated with the few exceptions our other members have found. So the answer to your question is, you have an error that reached the Last Chance handler and you had to do a RESET. And there went your variables.
