0 like 0 dislike
1 view
asked ago in Computer Science by (383k points)
How can save and closed an auto report?

1 Answer

0 like 0 dislike
answered ago by (383k points)
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.
Welcome to Free Homework Help, where you can ask questions and receive answers from other members of the community. Anybody can ask a question. Anybody can answer. The best answers are voted up and rise to the top. Join them; it only takes a minute:

8.6k questions

7.7k answers

24 comments

3.2k users

Categories

Free Hit Counters
...