Let your application take a rest - clean up with TimerJob

If you've ever tried to use Access in an object-oriented way you probably know that you can instantiate not only standard class modules but also forms and reports as they are class modules with an additional "base functionality".

This can be used, for example, to open the same form again and again in different windows to display different records of a table. The user can also close these windows normally using the close button of the window or some close button supplied by the application. So far, so good.

But what happens to the (closed) form object, the instance, in the background? If you release all variables pointing to the instance by setting it to "Nothing" you would expect that the form will now be unloaded, like most other objects you have instantiated on your own.
Unfortunately that's not the case, or not immediately. Access has collections for it's own objects for any instance, the "Forms" collection in this case, which holds an object pointer of all opened forms until they are closed using "DoCmd.Close" or the [X]-button of the window - or if no variable points to an instance of an opened form which was opened using the object oriented way ("Set frm = New Form_MyForm").
Access will do that, sure - but not when you expect that, it removes the form from the "Forms" collection only if there is any time to do that, when nothing else happens (and, following the help, that includes moving the mouse). That means, if your form uses i.e. a Timer event the event will go on running and executing code until Access decides to remove the form.

So, keeping that in mind the first thing you should consider is to always clean up any module level variable which is a reference to a complex object (like a recordset), especially if that object holds references on it's own (like in a recordset an open connection). The best place to do that is the "Close" or "Unload" event of a form. (In case of standard class modules you should use the "Class_Terminate" sub to do such things also.)
Next is, if you have used a Timer event in your form, set the "TimerInterval" property to 0 to prohibit any further execution of the timer.

If you have opened a form using "DoCmd.OpenForm" you can close it by using "DoCmd.Close". Both methods are using only one instance of the form and both are using the name of the form to identify the form, "Close" does that by searching for this name in the "Forms" collection.
But in case of multiple form instances in the object oriented way DoCmd.Close can not help you because it searches for the name only which is equal for all instances of the form, meaning it removes the first found form in the "Forms" collection only - which is surely not the right one in most cases. Unfortunately there is no other way to explicitly remove the wanted instance from the "Forms" collection with a VBA command.

So how can you make sure the closed instance (and only that one) will be removed from the "Forms" collection now? The solution is: Let Access do the job by giving your application a short rest. To do that, you have two possibilities: "DoEvents" and "DBEngine.Idle".

"DoEvents" is a command coming from Visual Basic and which is available in all VBA dialects (Word, Excel, ...). It is usually used to return the control back to the OS so that it can handle all open tasks collected in queues like processing keystroke messages for Access or reporting the OS that the application is still running (to avoid "not responding" messages in the task manager). Access can then also do pending tasks, like refreshing the screen and so on, so it is most likely that "DBEngine.Idle" will be called after "DoEvents" completed the OS tasks.

"DBEngine.Idle" can now be used to directly process things that Access wants to do (for detailed information what it does, see the VBA help), which is a method only exists in Access. One of these things is: Refreshing the memory - and that includes the garbage collector, a task in the background which removes unused objects and variables when the last reference to it is removed.

How can that be used now to remove the form from the "Forms" collection? Normally one form instance of multiple instances should be removed. But independent if you use the "Unload"/"Close" events or let the user click the [X] button or if you have code to remove the instance variable - the form remains in the "Forms" collection (you can see that if you go to the "Watch Window" and add "Forms" as watch by choosing "(All Procedures)" and "(All Modules)" as options and open the contents by clicking the [+] button in the watch window). When Access has time it will be removed. You could think of using "DBEngine.Idle" now and that's right, that will remove the instance - but the problem is, you cannot do that in the instance's code because that would mean the code is still running and the object is still active, so the form will remain in memory until the code execution is completed.

The solution is to use a Timer event to forward the job to another object which still exists after the calling object was closed. That can be done by using an invisible form which only have the purpose to run such jobs. I call it "frmTimerJob".

Create an empty form and save it as "frmTimerJob". Anything needed to get that work is in the following code, copy and paste that into the form's code module:

Option Compare Database
                      Option Explicit
                      
                      Public Enum EnmTimerJob
                          enmTJob_Idle
                          enmTJob_Idle_FreeLocks
                          enmTJob_Idle_RefreshCache
                          enmTJob_DoEvents
                      End Enum
                      
                      Private prv_TimerJob As EnmTimerJob
                      
                      Public Property Let TimerJob(intTime As Long, intJob As EnmTimerJob)
                          prv_TimerJob = intJob
                          Me.TimerInterval = intTime
                      End Property
                      
                      Private Sub Form_Load()
                          Me.Visible = False
                      End Sub
                      
                      Private Sub Form_Timer()
                          Select Case prv_TimerJob
                              Case enmTJob_Idle
                                  DBEngine.Idle   ' Amongst others start garbage collector to remove unused forms from the "Forms" collection
                              Case enmTJob_Idle_FreeLocks
                                  DBEngine.Idle dbFreeLocks
                              Case enmTJob_Idle_RefreshCache
                                  DBEngine.Idle dbRefreshCache
                              Case enmTJob_DoEvents
                                  DoEvents
                          End Select
                          Me.TimerInterval = 0
                      End Sub

Select all Open in new window

Now, in the "Close" or "Unload" event of the form instance you want to be removed from the "Forms" collection you can use this row to do that job (preferably as last row in the code after you have removed any reference to open objects of this form like described above):

Form_frmTimerJob.TimerJob(100) = enmTJob_Idle

Select all Open in new window

What it does: If you use "Form_YourFormName" VBA will open an instance of this form (only possible if the "HasModule" property of the form is True, which is automatically the case when you insert any code into the form). Here the form "frmTimerJob" will be loaded and then the value "enmTJob_Idle" will be assigned to the private variable "prv_TimerJob". The value in brackets is used to set a TimerInterval, that means, the job should be executed in 100 milliseconds.
After this row is executed in the still opened instance of the multiple-instance-form, this form will be closed now and the last reference should be gone now, but the form is still in the "Forms" collection. After waiting a loooong time of 100 milliseconds (it's really long for your computer) the Timer event of the "frmTimerJob" form will execute and read out the private variable to see what it should do - in this case executing "DBEngine.Idle". And if you are still looking at the watch window you'll see that the form instance is now removed from the "Forms" collection.

The TimerJob-Enum is used to have an easy to read method of assigning the wanted job. I added more jobs here to show how you can handle any job you want.

You'll also see that the form "frmTimerJob" will remain in memory even if you call DBEngine.Idle manually in the immediate window of VBA but that doesn't matter, it will be unloaded when you close it using DoCmd.Close or if you close Access. But it's not needed as it doesn't cost much resources, let it remain in memory, the next time you call a TimerJob it will be reused.

By the way: "DBEngine.Idle" also works in ADPs although the JET/ACE engine of Access is not used here.

Have fun in experimenting with the TimerJob form.

Christian