Data Management

Automatically close all the open forms and reports in an Access database

Before closing a database, it's a good idea to close all the open forms and reports - but you can't be sure your users will always do it. Luckily, you can have Access execute a simple cleanup routine to handle the task.

You might expect your users to clean up after themselves by closing all the open forms and reports before closing the database. The truth is, they probably won't bother -- and frankly, it really isn't their job. Instead of relying on users, add the appropriate code to the database's exit routine. Your users won't know the difference.

Note: This article is available as a PDF download, along with a text file containing the code used in the example presented here.

The For...Each statement doesn't work

At first, you might think that VBA's For...Each statement is the most efficient way to close all open forms or reports. For instance, the following code should loop through the collection of open forms and close each until all the forms are closed -- or so you might think:

Function CloseForms()   'Close all open forms   Dim frm As Form   For Each frm In Forms     DoCmd.Close acForm, frm.Name   Next End Function

However, this function always leaves one form open. (The same is true if you loop through the Reports collection.) That's because after closing a form, the remaining forms slip down a notch in the collection. It's easy to see with a simple illustration. Suppose you open the following forms in order: Employees, Products, and Orders. Furthermore, the collection's index values for these three forms are as follows:

Employees       0

Products          1

Orders             2

The For loop goes through the collection in the same order the forms were open. During the first loop, the code deletes the form at the 0 index position, Employees. Consequently, Products and Orders both move down a notch: Products is now 0 and Orders is 1. The next time through, the loop is looking for 1, so it deletes Products. Now, Orders slips down to 0, but the loop is looking for the index value 2, which it doesn't find. The loop finishes without closing the Orders form.

A simple loop will do it

After eliminating For...Each, you might consider a For loop based on the number of forms, but there's a simpler way. The Do loop in Listing A keeps running until there are no forms left in the collection. It's simple and efficient. It still relies on the index value, but that value is always 0, and there will always be a form or report in that position until all the forms or reports are deleted and the respective collection is empty.

Listing A

Function CloseFormsReports()   'Close all open forms   On Error GoTo errHandler   Do While Forms.Count > 0     DoCmd.Close acForm, Forms(0).Name   Loop   Do While Reports.Count > 0     DoCmd.Close acReport, Reports(0).Name   Loop   Exit Function errHandler:   MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error" End Function

How to execute it

The form and report closing code is simple; deciding how to execute it might prove more difficult. You could include a button or menu and let the users decide, but that's not a great idea. If you want to clean up before closing, let the user interface execute it.

For instance, the Main Switchboard form in Northwind (the sample database that comes with Access) has an exit button. You could easily call the above function from that button's Click event. Or you could simply add the code to the event as follows:

  1. Open the Main Switchboard form in Design view and then click the Code button to launch the form's module.
  2. From the Object drop-down list (in the Module window), choose ExitMicrosoftAccess (that's the name of the form's exit button).
  3. From the Procedure drop-down list, choose Close. The Visual Basic Editor (VBE) will insert a stub for the form's Close event.
  4. Insert the two Do loops just above the DoCmd.Quit statement, as shown in Figure A.
  5. Click the Save button on the VBE's Standard menu.

Figure A

You can add the form and report closing code to the existing user interface routine that closes the database.

To try it out, return to Access and open a few forms and reports. Now, click the Exit Microsoft Access button on the Main Switchboard form. It'll happen quickly and you won't actually see Access close all the open forms and reports, but it will.

This is just one simple example of how to execute the function. Keep in mind that closing the form or report will execute that object's Close event, if any. Be sure to test the code thoroughly and add the appropriate error-handling code. While a bit of code in a form's or report's Close event shouldn't be troublesome, it's something to consider during the development stage. If the Close event performs some complex tasks, you might want to move that functionality to another area.

Worth noting

The Northwind database doesn't force users to use the Main Switchboard form to close the database. That means they could bypass any cleanup routine executed by the Exit button. If you rely on the user interface to execute cleanup or maintenance code, you must make sure users actually use that routine to exit the database. So you must inhibit the Close button on the title bar and the Close and Exit commands on the File menu. Don't forget about the Close button on the Database window's title bar. Clicking it will close the current database if users have access to the Database window.

Quitting time!

Before closing a database, it's a good idea to close all the open objects. To close all open forms and reports, you can execute a simple Do loop from the user interface before closing the database.


Miss an Access tip?

Check out the Microsoft Access archive and catch up on other Access tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

5 comments
sring
sring

I just found this code and after 4 years it WORKS !!!! Just what I needed since I have over 30 reports and didn't want to go through each one and close it separately.

dsomerv
dsomerv

1) It sounds like a good idea to close forms, but what happens if you don't? 2) I thought that access did all the housekeeping - such as closing forms , at exit time. Have you done any experiments to show that this is untrue ? 3) If VBA code in a form is executing, what happens when that form is closed ? What about the form in which the proposed tidying code is executing ? 4) Do the forms need to be closed in the reverse order of their opening ? For example, if form A opens B and C and B opens D and E and C opens F and G, does it matter if the order is ABCDEFG or DECFGBA ?

ssharkins
ssharkins

It isn't necessary to close all the objects before closing database -- however, depending on Access is iffy... I still do it on production work, but not my own -- kind of the cobbler's children have no shoes. :) I just don't bother with my own stuff. Normally, cleanup should occur when the user's done with each form or report -- you'd think. I know from experience that this isn't always as practical or as easy as it sounds. However, cleaning up in a busy database can happen at anytime, not just when closing down. My advice... close objects when the user's done with them. When not possible, or if you're dealing with a legacy application and that's going to be a lot of work, add code to close them all periodically -- just be careful because sometimes those objects are open for a reason and you might break something.

ainow
ainow

Susan, When calling a procedure modCleanup from Close_Form in frmMenu I receive the following error message: 2501: The Close action was canceled. Public Sub Cleanup() varX = CloseFormsReports ' DoCmd.Quit End Sub Function CloseFormsReports() On Error GoTo errHandler Debug.Print "Forms: " & Forms.Count Do While Forms.Count > 0 Debug.Print Forms(0).Name DoCmd.Close acForm, Forms(0).Name Loop Debug.Print "Reports: " & Reports.Count Do While Reports.Count > 0 DoCmd.Close acReport, Reports(0).Name Loop Exit Function errHandler: MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error" End Function Debug results: Forms: 4 frmMenu

Editor's Picks