Manipulating Reports and Report Controls Programmatically

Access offers two levels of entry to reports, forms, and other important database objects. In Chapter 5, you learned about the AllForms collection. Access also has an AllReports collection—as well as AllTables, AllQueries, AllMacros, AllViews, AllModules, AllStoredProcedures, AllDataAccessPages, and AllDataDiagrams collections. A member of any of these collections is an AccessObject object (a new type of object in Access 2000). You can refer to an AllReports member by one of three conventions:

AllReports (0) AllReports ("name") AllReports![name]

Enumerating Reports

Your code can enumerate AccessObject objects in any of the All collections to determine whether objects are in a database connection. It does not matter whether the object is open or closed. You can also determine whether an object is loaded. When an AccessObject object is loaded or open, your application can work with a parallel collection space. Such collections are all the open reports, forms, and so forth in an Access database. Members of the Reports collection are individual reports that are open in an application. These open Report objects expose all the properties available through VBA instead of the much more restricted set in the AllReports collection. You can use the Name property in AllReports and Reports to move between the two parallel collections. 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.

The ListAllReports procedure below enumerates the members of the AllReports collection while listing their name and loaded status. The AllReports collection members belong to either the CurrentProject or the CodeProject, which are members of the Application object. You must reference one of these 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 below show how to program both collections when they point at another project. Notice the similarity of the code between collections as well as between the current project and another project.

The ListAllFormsElsewhere procedure below prints the total number and the names of individual members in the AllForms collection for Chapter5.mdb. This file is on the book's companion CD. The procedure assumes that you load the file from the CD and store it in your My Documents folder. It enumerates AccessObject members in another database 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:\Programming Access\" & _         "Chap05\Chapter5.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 End Sub

The ListAllReportsElsewhere procedure below follows the same general design as the preceding one, although it deals with the AllReports collection instead of the AllForms collection and uses the Northwind.mdb database instead of Chapter 5.mdb. The layout is nearly identical except for the use of string variables to define the database name. This change is strictly for convenience and generality—nothing in Access or VBA mandates the use of strings.

Sub ListAllReportsElsewhere() Dim appAccess1 As Access.Application Dim obj1 As AccessObject Dim srtPath 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\Office\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 End Sub 

Modifying Report Control Properties

Your application code can use the AllReports collection as a pathway to individual open reports and the controls on them. Once you pass through the pathway, your application can read and modify the properties of individual open reports. The ControlsInReports procedure drills down from the AllReports collection members to the text box and label properties on individual open reports.

Sub ControlsInReports() Dim obj1 As AccessObject, ctl1 As Control     For Each obj1 In CurrentProject.AllReports         If obj1.IsLoaded = True Then             For Each ctl1 In Reports(obj1.Name)                 If ctl1.ControlType = 100 Then                     Debug.Print ctl1.Name, ctl1.Caption                 ElseIf ctl1.ControlType = 109 Then                     Debug.Print ctl1.Name, ctl1.Value                 Else                     Debug.Print ctl1.Name & " is not a" & _                         " label or a text box."                 End If             Next ctl1         End If     Next obj1 End Sub

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. It is important to know the type because it determines the properties that the control exposes. For example, a label control displays its Caption property, but a text box uses a Value property to depict what it shows. You can use the Object Browser in VBE to view the numeric codes of other control types that you want to edit or examine.

Mailing Snapshots

The sample on the facing page enumerates reports to determine whether they are marked for mailing as snapshot files. The sample relies on two procedures. First, the SendSnapShots procedure enumerates the members of the AllReports collection. Since the code checks 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 only available 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 get feedback as the second procedure runs. This is particularly appropriate for long reports for which it can take a while for the snapshot file to be created and mailed.

Sub SendSnapShots() Dim obj1 As AccessObject, 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 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, "[e-mail]", , , _         "Snapshot Report", "Here is the report.", False     End If      End Sub

The CheckMailItTag procedure accepts the report name passed to it by SendSnapShots. It uses this name to create a reference to the Reports collection member with the same name. Then it checks the Tag property of the report to determine whether it equals "mail it". If so, the procedure invokes DoCmd's SendObject method to create a snapshot file and send it to an e-mail address (in this case, [e-mail]). You can replace the string constant for the address with any single address or series of addresses that your application requires. It is important that the argument after the message body be False. With 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 operator intervention.