Enumerating Reports

Your code can enumerate AccessObject objects in any of the Allxxx collections to determine whether objects exist in a database connection. It does not matter whether the object is open or closed. When an AccessObject object is loaded or open, your application can work with corresponding collections that encompass all the open reports, forms, and other important objects in an Access database. For example, members of the Reports collection are individual reports that are open in an application. These open Report objects contain a richer set of properties than the more restricted set of objects in the AllReports collection. In addition, members of the Reports collection employ methods that the AllReports collection members do not make available. You can use the Name property in AllReports and Reports to identify a particular object in either collection. By using the IsLoaded property in the AllReports collection, you can verify whether you need to open a report before attempting to manipulate its properties and methods using the Reports collection.

The ListAllReports procedure that follows enumerates the members of the AllReports collection listing each report's name and loaded status. The AllReports collection belongs to either the CurrentProject or the CodeProject. CurrentProject and CodeProject are members of the Application object. You must reference one of these two members to expose the AllReports members. Therefore, the ListAllReports procedure starts by setting a reference to the CurrentProject member of the Application object. You need this reference to reach the members of the AllReports collection. Notice that the For…Each loop passes through each AccessObject object (obj1) in AllReports, but the path to AllReports starts with the reference to Application.CurrentProject.

Sub ListAllReports()
Dim obj1 As AccessObject, app1 As Object
   
'Create a reference to the current project instance
Set app1 = Application.CurrentProject
   
'List each report in the application, and
'describe as loaded or not
For Each obj1 In app1.AllReports
    If obj1.IsLoaded = True Then
        Debug.Print obj1.Name & " is loaded."
    Else
        Debug.Print obj1.Name & " is not loaded."
    End If
Next obj1
   
End Sub

The AllReports and AllForms collections are directly analogous to one another. You are not restricted to examining AccessObject members in the active project. The ListAllFormsElsewhere and ListAllReportsElsewhere procedures, which you'll see in a moment, show how to program both collections when they point at another project. Notice the similarity between the code that manipulates the two collections as well as between the procedure shown above, which works with the current project, and the two procedures shown below, which work with another project.

The ListAllFormsElsewhere procedure shown next prints the total number and the names of individual members in the AllForms collection for the Northwind database file from the Chapter06.mdb file. This procedure demonstrates how to enumerate AccessObject objects in the Northwind.mdb file.

Sub ListAllFormsElsewhere()
Dim appAccess1 As Access.Application
Dim obj1 As AccessObject
   
'Create a reference to another database file
Set appAccess1 = New Access.Application
appAccess1.OpenCurrentDatabase _
    "C:\Program Files\Microsoft Office\" & _
    "Office11\Samples\Northwind.mdb"
   
'Print the total number of forms in the database
Debug.Print appAccess1.CurrentProject.AllForms.Count
For Each obj1 In appAccess1.CurrentProject.AllForms
    Debug.Print obj1.Name
Next obj1
   
'Clean up objects
appAccess1.Quit
Set appAccess1 = Nothing
   
End Sub

The ListAllReportsElsewhere procedure shown next follows the same general design as the preceding one, although it deals with the AllReports collection instead of the AllForms collection. The layout is nearly identical except for the use of string variables to define the database name. This change is strictly for convenience and to make the code more generally applicable—nothing in Access or VBA mandates the use of a string variable instead of a string constant in the call to the OpenCurrentDatabase method.

Sub ListAllReportsElsewhere()
Dim obj1 As AccessObject
Dim strPath As String, strFile As String, strDBName As String
   
'Create a reference to another database file
Set appAccess1 = New Access.Application
strPath = "C:\Program Files\Microsoft Office\" & _
    "Office11\Samples\"
strFile = "Northwind.mdb"
strDBName = strPath & strFile
appAccess1.OpenCurrentDatabase strDBName
   
'Print the total number of reports in the database
Debug.Print appAccess1.CurrentProject.AllReports.Count
For Each obj1 In appAccess1.CurrentProject.AllReports
    Debug.Print obj1.Name
Next obj1
   
'Clean up objects
appAccess1.Quit
Set appAccess1 = Nothing
   
End Sub

Modifying Report Control Properties

Your application code can use the AllReports collection as a pathway to individual reports that are open and to their controls. Using this pathway, your application can read and modify the properties of these open reports and their controls. The ControlsInReports procedure (shown next) drills down from the AllReports collection members to the text box and label properties on individual reports that are open.

The ControlsInReports procedure starts with a For…Each loop that iterates through the members of the AllReports collection. If a member is open, as indicated by a value of True for its IsLoaded property, the code enters a nested For…Each loop to enumerate the controls on that report. You can use the ControlType property to determine a control's type. You need to know the control type because this determines the properties that the control exposes. For example, a label control displays its Caption property, but a text box displays its Text or Value property. You can use the Object Browser in the VBE to view the intrinsic constants of other control types that you want to edit or examine. Forms and reports share the same set of control types. For the full set of control types from the Object Browser, see Figure 5-24 in Chapter 5.

This program contains an error trap in case the procedure loops to a report that's open in Design view. A report open in Design view has an IsLoaded property value of True. However, the display value of a text box is not available in Design view. Attempting to print or otherwise access this report generates an Err object with a Number property of 2186. The solution is to open the report in Preview mode. Then, when the procedure completes printing the text box values, the code restores the Design view for the report.

Sub ControlsInReports()
On Error GoTo ControlsInReports_Trap
Dim obj1 As AccessObject
Dim ctl1 As Control
Dim bol1 As Boolean
   
'Loop through the reports in the Access database file
'or Access project
For Each obj1 In CurrentProject.AllReports
    If obj1.IsLoaded = True Then
Start_Printing:
'If the report is open, loop through the report's
'controls and print property values for label and
'text box controls
        For Each ctl1 In Reports(obj1.Name).Controls
            If ctl1.ControlType = acLabel Then
                Debug.Print ctl1.Name, ctl1.Caption
            ElseIf ctl1.ControlType = acTextBox Then
                Debug.Print ctl1.Name, ctl1.Value
            Else
                Debug.Print ctl1.Name & " is not a" & _
                    " label or a text box."
            End If
        Next ctl1
'Restore Design view if the procedure changed the view
   
        If bol1 = True Then
            DoCmd.OpenReport obj1.Name, acViewDesign
            bol1 = False
        End If
    End If
Next obj1
   
ControlsInReports_Exit:
Exit Sub
   
ControlsInReports_Trap:
If Err.Number = 2186 Then
'Open in Preview mode if Design view generates error
    DoCmd.OpenReport obj1.Name, acViewPreview
    bol1 = True
    Resume Start_Printing
Else
    Debug.Print Err.Number, Err.Description
End If
   
End Sub

Mailing Snapshots

The next sample enumerates reports to determine whether they are marked for mailing as snapshot files. The sample relies on two procedures: SendSnapshots and CheckMailItTag. First, the SendSnapshots procedure enumerates the members of the AllReports collection. In order to check whether the report's Tag property is "mail it", the report must be open. The Tag property is not available through the AllReports collection—it is available only through the Reports collection. The SendSnapshots procedure checks the IsLoaded status of each AllReports member. If the report is loaded, the procedure calls the CheckMailItTag procedure. If IsLoaded has a value of False, the procedure opens the report before calling the second procedure.

The sample does not call the Echo method with a False parameter, so a user can easily obtain feedback as the second procedure runs. This is particularly appropriate in cases where it takes a while to create and mail the snapshot file. By the way, in the collection of reports in the Chapter06.mdb file, only one report (rptOrdersByMonth) has a setting of "mail it" for its Tag property.

The CheckMailItTag procedure accepts the report name passed to it by SendSnapshots. CheckMailItTag uses this report name to create a reference to the Reports collection member with the same name. Then, CheckMailItTag checks the Tag property of the report to determine whether it equals "mail it". If it does, the procedure invokes the DoCmd object's SendObject method to create a snapshot file and send it to an e-mail address (in this case, virginia@cabinc.net). You can replace the string constant for the address with any single address or series of addresses that your application requires. Be sure that the argument after the message body ("Here is the report.") remains False. If you use the default value of True, your procedure will halt with the message open and wait for the user to edit the message. Setting the value to False enables the procedure to loop through all the reports without any user intervention.

Sub SendSnapshots()
Dim obj1 As AccessObject
Dim app1 As Object
   
'Create a reference to the current project instance
    Set app1 = Application.CurrentProject
    
'Enumerate each member in AllReports to verify if loaded.
'If not loaded, open before calling CheckMailItTag.
    For Each obj1 In app1.AllReports
        If obj1.IsLoaded = True Then
            CheckMailItTag obj1.Name
        Else
            DoCmd.OpenReport obj1.Name, acViewPreview
            CheckMailItTag obj1.Name
            DoCmd.Close acReport, obj1.Name, acSaveNo
        End If
    Next obj1
   
End Sub 
   
'Open Outlook before invoking this procedure
Sub CheckMailItTag(obj1name)
Dim rep1 As Report
   
'Set reference to Reports member corresponding
'to AllReports member
    Set rep1 = Reports(obj1name)
   
'If Tag property says "mail it",
'create a snapshot file and mail it
    If rep1.Tag = "mail it" Then
        DoCmd.SendObject acOutputReport, obj1name, _
        acFormatSNP, "virginia@cabinc.net", , , _
        "Snapshot Report", "Here is the report.", False
    End If
    
End Sub