Closing All Open Forms

<< Click to Display Table of Contents >>

Navigation:  Smart Access 1996-2006 > Jun-1997 >

Closing All Open Forms

Ken Getz            
 
Ken recently taught an Access 97 training class, and got a few interesting questions from the experience. The two questions answered here are the direct outcome of that class.
 
I'm trying to find a way to close all the forms in my application except the current form. That is, I have code attached to the Click event of a button on the form, and I'd like to have that button click close all the other forms. I've tried a number of alternatives, but I either get runtime errors or other weird stuff happens. Can you help?
 

ad3468x60

 
This is a common question -- it comes up almost every time I teach Access training classes for developers. There are a number of issues here, including these:

How do you close all the forms that are currently open?

How can you tell which form is the current one, and avoid closing it?

 
Let's take these two issues one at a time. This answer will attempt a number of different solutions, all based on forms in the sample database. In each case, opening one of the test forms (frmCloseAll1, frmCloseAll2, and so on) will open four subsidiary forms. Clicking the button on the test form calls code, which I'll describe here, that attempts to close all the subsidiary forms.
 
Closing all open forms
 
To simplify matters, let's first look into closing all the open forms. That should be a simple task: Access provides a Forms collection that contains one reference for each currently open form. You should be able to loop through the collection one item at a time and close each form:
 

' Code from frmCloseAll1

Dim intI As Integer

 

For intI = 0 To Forms.Count - 1

  DoCmd Close A_FORM, Forms(intI).Name

Next intI

 
 
(The syntax for the Access 95/97 example is a bit different, but the ideas are the same.) If you open frmCloseAll1 and click the Close All Forms button, you'll receive a runtime error (2456: Invalid form number reference) in response. The code sure seems like it ought to work, but it doesn't. Why not? It's all because of the way Access treats objects in collections.
 
All the items within a collection are numbered -- that is, as you add them to the collection, they're assigned an ordinal position within the collection. As you remove them from the collection, Access renumbers the items remaining within the collection. Figure 1 shows what happens, in memory, as you run the previous code fragment. As you can see, Access renumbers the elements of the collection as you close forms, so that by the time it attempts to close form 3, there is no form number 3, and the code fails.

199706_kg1
Figure 1
 
Clearly, this technique won't work. If you think about it for a minute, there are two reasonable alternatives: You can loop through the forms, closing form 0 on each iteration; or you can loop through the Forms collection backwards, closing the forms in reverse order. The first technique, demonstrated in frmCloseAll2, uses the following code to close the forms:
 

' Code from frmCloseAll2

Dim intI As Integer

 

For intI = 0 To Forms.Count - 1

  DoCmd Close A_FORM, Forms(0).Name

Next intI

 
 
The second technique, from frmCloseAll3, uses the following code:
 

' Code from frmCloseAll3

Dim intI As Integer

 

For intI = Forms.Count - 1 To 0 Step -1

  DoCmd Close A_FORM, Forms(intI).Name

Next intI

 
 
Which is better? Certainly not the first method (from the earlier frmCloseAll1) -- it doesn't work at all. Of the next two, I'd recommend the method used by frmCloseAll3. This technique, closing the forms in reverse order, doesn't require Access to renumber the forms with each loop iteration, and therefore seems to cause less effort for Access. Of course, the difference in speed between the two methods is going to be inconsequential, at best.
 
If you're using Access 95 or 97, and you've looked into the For Each...Next control structure, it seems that this would be a perfect place to use it. That is, you'd expect the following code to do the trick:
 

' Code from frmCloseAll3a (32-bit version only)

Dim frm As Form

 

For Each frm In Forms

  DoCmd.Close acForm, frm.Name

Next frm

 
 
You'd be wrong (as was I, when I truly expected this to work). This doesn't cause a runtime error, but it leaves the same two forms open once it's completed. Contrary to what you may think, the For Each loop doesn't dynamically change its scope while the loop is executing. As you delete items from the collection (by closing them), the loop pointer keeps marching along, effectively skipping some forms along the way. No matter which version of Access you're using, the technique used by frmCloseAll3 works best.
 
Skipping the current form
 
As you've noticed if you run frmCloseAll3, the previous code fragments close all the forms and don't attempt to bypass the form running the code. If your application requires you to close all but the current form, you'll need to take an extra step. There are at least two solutions to this particular problem, which I'll outline here.
 
Somehow, as your code loops through all the open forms, you'll need to find some way to bypass the current form. Because this code is running from the form's module, you can use the form's Me property (a reference to the current form) to refer to the form. The only issue, then, is to find something that uniquely identifies each form and compare it to the corresponding value for the current form as you loop through all the forms.
 
The form's Name property will do, and frmCloseAll4 uses the following code to check, in each iteration, whether it should close the particular form:
 

' Code from frmCloseAll4

Dim intI As Integer

Dim strName As String

 

For intI = Forms.Count - 1 To 0 Step -1

  strName = Forms(intI).Name

  If Me.Name <> strName Then

    DoCmd Close A_FORM, strName

  End If

Next intI

 
 
If, on a particular loop iteration, the current form's name (Me.Name) is the same as the name of the form the code is trying to close (Forms(intI).Name), then the loop skips that particular form. Otherwise, it closes all the rest.
 
However, something's bothering me here -- there has to be a better way! All you really care about is whether Me and Forms(intI) refer to the same form. Sure, you can check the Name property of each, but that seems like extra work. Of course, there is a better way: the Is operator comes to the rescue. This seldom-discussed operator allows you to compare two object references and to find out if the references refer to the same object. Rather than comparing the Name properties of the two references, you should be able to use code like this:
 

' Yes, this sounds grammatically bizarre, 

' but it works!

If Me Is Forms(intI) Then

  ' You know Forms(intI) refers to 

  ' the current form, Me.

End If

 
 
Using the Is operator, you can compare two references and see if they refer to the same object. However, in this case you want to know whether the two references point to different objects (so it's safe to close the form). In that case, you'll use code like this:
 

If Not Me Is Forms(intI) Then

 
 
Therefore, the final version of the sample form, frmCloseAll5, uses the following code to close all but the current form:
 

' Code from frmCloseAll5

Dim intI As Integer

 

For intI = Forms.Count - 1 To 0 Step -1

  If Not Me Is Forms(intI) Then

    DoCmd Close A_FORM, Forms(intI).Name

  End If

Next intI

 
 
Any differences in speed between the two final methods are imperceptible, of course, but using the Is operator seems so much more elegant than comparing properties of the two objects.
 
 
 
Read about how to get the download GETZ44.EXE on this page

 

The second part of this Access Answer Column is here
Handling Misleading Report Errors_2
 

You may like to read this tip page instead Closing All Forms