I have recently changed an app to make heavy use of custom events. There is a class module containing all the events, and subroutines containing generally the single line of code to raise the event, since VBA does not allow events to be raised directly, but insists that they must be raised only in the class module containing the event definition. I have to call a separate routine in the class that then raises the event, instead of raising it directly via the class module variable. I think that is a rather pointless restriction, but that is another issue.
Forms that make changes to auxiliary tables all call these routines via a global event variable pointing to a single instance of this class. I instantiate that by setting a global variable upon app startup, I prevent erroneous multiple instantiations in the class by checking the global variable's status in the Initialize procedure, and all code raising or handling custom events uses that one instance:
I tested all this very thoroughly before deploying it, and everything worked great. But I've suddenly started running into a strange problem, which didn't happen when I first wrote and tested it all, where the WithEvents variable is seemingly not properly disposed of when the form closes. On the first use of a form, all is fine. On the second and subsequent uses of a form, any action that raises a custom event from the datasheet causes the event procedure in the edit form to be called multiple times – as many times as the parent form has previously been opened. It does not work the other direction - events from the edit form do not trigger multiple calls in the datasheet form, despite the coding being pretty much identical. I am of course familiar with the double call to the OnCurrent event code, but this is new to me. There is no doubt about what is happening – even when the very first line in the custom event handler is simply:
so that there is no possibility of recursive calling, it prints this, as many times as the form has been opened in this session. Code referring to the form's instance, like Me.Name, throws error 2467, reference to a closed or non-existent object on every superfluous call, while the base call continues to work properly. For example, if the form is on its third time being called, the event handler cycles through three times. The first and second time through, the Me... references fail, but the third time, they all work normally. Apparently the 'extra' calls go first, and the 'real' one is last. Checking the WithEvents variable on form load confirms that it is nothing, no matter how many times the form has been called before. It is always a local variable in that form, of course, defined in the first few lines of the form, before any procedures.
The entire app structure is one main form, with command buttons that open new forms in dialog mode. Those new forms are unbound, always with two subforms – one bound and the other not. It is the classic, read-only bound datasheet view in one, unbound editing of a single record in the other – what Access's split form feature was supposed to accomplish, but I could never get that to work properly.
I wanted to experiment more with custom events, now that I finally got them all set up the way I liked, so pretty much everything is run by events. Specifically, when the user clicks on a record in the datasheet subform, it fires a custom event that the edit form reacts to. The datasheet passes some information about the current record via parameters of the event, and the edit form's custom event handler loads that record. When the user finishes editing that record and clicks a button to save his work, the record is written back to the table and an event is raised to announce that the table has changed. The datasheet reacts to that event by reloading the datasheet, and any other forms that are open right then also react, doing things like reloading comboboxes, if they are sourced from that table. All this works great, except for this sudden compounding of phantom event variables on repeated form openings. And only some forms do this. I have 12 such popup forms in the app, all editing different aux tables, and they all work as close to identically as I was able to make them. They have the same appearance, the same layout of controls, the same code handling the same events, but 9 of them do this weird stacking and 3 do not.
Despite the WithEvents variable being nothing on form startup, explicitly setting it to nothing on the prior form close makes this ghost instancing stop accumulating. I suppose that is a solution, but it seems to me this should not be happening. This is exactly the kind of nonsense that leads to 'voodoo programming', like the notion, I think now antiquated, that all object variables should be explicitly manually destroyed on module exit. I still see advice to that effect, and I also see advice that claims this is rubbish, that this many years in, VBA knows when something goes out of scope and cleans up properly. But it does not appear to be doing so here.
Naturally, all this resets when the app is closed and reopened.
Forms that make changes to auxiliary tables all call these routines via a global event variable pointing to a single instance of this class. I instantiate that by setting a global variable upon app startup, I prevent erroneous multiple instantiations in the class by checking the global variable's status in the Initialize procedure, and all code raising or handling custom events uses that one instance:
- any time something happens that requires external notification, the appropriate sub from this class is called via that global variable, and every custom event is raised in that one class module;
- every form that must react to such changes has a local WithEvents class variable, and on form load, that local WithEvents variable is set to point to the global one, again, so that everything goes through that one instance of the class.
I tested all this very thoroughly before deploying it, and everything worked great. But I've suddenly started running into a strange problem, which didn't happen when I first wrote and tested it all, where the WithEvents variable is seemingly not properly disposed of when the form closes. On the first use of a form, all is fine. On the second and subsequent uses of a form, any action that raises a custom event from the datasheet causes the event procedure in the edit form to be called multiple times – as many times as the parent form has previously been opened. It does not work the other direction - events from the edit form do not trigger multiple calls in the datasheet form, despite the coding being pretty much identical. I am of course familiar with the double call to the OnCurrent event code, but this is new to me. There is no doubt about what is happening – even when the very first line in the custom event handler is simply:
Code:
Debug.Print "Ext.";:Exit SubThe entire app structure is one main form, with command buttons that open new forms in dialog mode. Those new forms are unbound, always with two subforms – one bound and the other not. It is the classic, read-only bound datasheet view in one, unbound editing of a single record in the other – what Access's split form feature was supposed to accomplish, but I could never get that to work properly.
I wanted to experiment more with custom events, now that I finally got them all set up the way I liked, so pretty much everything is run by events. Specifically, when the user clicks on a record in the datasheet subform, it fires a custom event that the edit form reacts to. The datasheet passes some information about the current record via parameters of the event, and the edit form's custom event handler loads that record. When the user finishes editing that record and clicks a button to save his work, the record is written back to the table and an event is raised to announce that the table has changed. The datasheet reacts to that event by reloading the datasheet, and any other forms that are open right then also react, doing things like reloading comboboxes, if they are sourced from that table. All this works great, except for this sudden compounding of phantom event variables on repeated form openings. And only some forms do this. I have 12 such popup forms in the app, all editing different aux tables, and they all work as close to identically as I was able to make them. They have the same appearance, the same layout of controls, the same code handling the same events, but 9 of them do this weird stacking and 3 do not.
Despite the WithEvents variable being nothing on form startup, explicitly setting it to nothing on the prior form close makes this ghost instancing stop accumulating. I suppose that is a solution, but it seems to me this should not be happening. This is exactly the kind of nonsense that leads to 'voodoo programming', like the notion, I think now antiquated, that all object variables should be explicitly manually destroyed on module exit. I still see advice to that effect, and I also see advice that claims this is rubbish, that this many years in, VBA knows when something goes out of scope and cleans up properly. But it does not appear to be doing so here.
Naturally, all this resets when the app is closed and reopened.
