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