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.
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
Products
Orders
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:
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.