Manipulating Reports and Report Controls


Manipulating Reports and Report Controls

In Chapter 5, you learned about the AllForms collection. Access also provides an AllReports collection as well as the AllTables , AllQueries , AllMacros , AllViews , AllModules , AllStoredProcedures , AllDataAccessPages , and AllDataDiagrams collections. A member of any of these collections is an AccessObject object. Microsoft first introduced AccessObject objects with 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 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 .

 SubListAllReports() Dimobj1AsAccessObject,app1AsObject     'Createareferencetothecurrentprojectinstance Setapp1=Application.CurrentProject     'Listeachreportintheapplication,and 'describeasloadedornot ForEachobj1Inapp1.AllReports Ifobj1.IsLoaded=TrueThen Debug.Printobj1.Name& " isloaded." Else Debug.Printobj1.Name& " isnotloaded." EndIf Nextobj1     EndSub 

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.

 SubListAllFormsElsewhere() DimappAccess1AsAccess.Application Dimobj1AsAccessObject     'Createareferencetoanotherdatabasefile SetappAccess1=NewAccess.Application appAccess1.OpenCurrentDatabase_  "C:\ProgramFiles\MicrosoftOffice\" &_  "Office11\Samples\Northwind.mdb"     'Printthetotalnumberofformsinthedatabase Debug.PrintappAccess1.CurrentProject.AllForms.Count ForEachobj1InappAccess1.CurrentProject.AllForms Debug.Printobj1.Name Nextobj1     'Cleanupobjects appAccess1.Quit SetappAccess1=Nothing     EndSub 

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.

 SubListAllReportsElsewhere() Dimobj1AsAccessObject DimstrPathAsString,strFileAsString,strDBNameAsString     'Createareferencetoanotherdatabasefile SetappAccess1=NewAccess.Application strPath= "C:\ProgramFiles\MicrosoftOffice\" &_  "Office11\Samples\" strFile= "Northwind.mdb" strDBName=strPath&strFile appAccess1.OpenCurrentDatabasestrDBName     'Printthetotalnumberofreportsinthedatabase Debug.PrintappAccess1.CurrentProject.AllReports.Count ForEachobj1InappAccess1.CurrentProject.AllReports Debug.Printobj1.Name Nextobj1     'Cleanupobjects appAccess1.Quit SetappAccess1=Nothing     EndSub 

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.

Note  

For a TextBox control, the Text property returns or sets the formatted current value of a control. The Value property is the saved value of a TextBox control. These properties are often the same, but they can diverge. For example, when you change the Text property without committing the change, Text and Value properties can be different for a TextBox control.

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.

 SubControlsInReports() OnErrorGoToControlsInReports_Trap Dimobj1AsAccessObject Dimctl1AsControl Dimbol1AsBoolean     'LoopthroughthereportsintheAccessdatabasefile 'orAccessproject ForEachobj1InCurrentProject.AllReports Ifobj1.IsLoaded=TrueThen Start_Printing: 'Ifthereportisopen,loopthroughthereport's 'controlsandprintpropertyvaluesforlabeland 'textboxcontrols ForEachctl1InReports(obj1.Name).Controls Ifctl1.ControlType=acLabelThen Debug.Printctl1.Name,ctl1.Caption ElseIfctl1.ControlType=acTextBoxThen Debug.Printctl1.Name,ctl1.Value Else Debug.Printctl1.Name& " isnota" &_  " labeloratextbox." EndIf Nextctl1 'RestoreDesignviewiftheprocedurechangedtheview     Ifbol1=TrueThen DoCmd.OpenReportobj1.Name,acViewDesign bol1=False EndIf EndIf Nextobj1     ControlsInReports_Exit: ExitSub     ControlsInReports_Trap: IfErr.Number=2186Then 'OpeninPreviewmodeifDesignviewgenerateserror DoCmd.OpenReportobj1.Name,acViewPreview bol1=True ResumeStart_Printing Else Debug.PrintErr.Number,Err.Description EndIf     EndSub 

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.

Note  

Make sure that Microsoft Outlook is open before invoking the DoCmd.SendObject command in the CheckMailItTag procedure. Otherwise, the SendObject method will try to launch Outlook and link to the Internet as if being launched for the first time. In any event, Outlook will likely query whether you want someone to send mail on your behalf . The "someone" in this case is the CheckMailItTag procedure. See Chapter 9 for samples that open Outlook and answer the query automatically.

 SubSendSnapshots() Dimobj1AsAccessObject Dimapp1AsObject     'Createareferencetothecurrentprojectinstance Setapp1=Application.CurrentProject 'EnumerateeachmemberinAllReportstoverifyifloaded. 'Ifnotloaded,openbeforecallingCheckMailItTag. ForEachobj1Inapp1.AllReports Ifobj1.IsLoaded=TrueThen CheckMailItTagobj1.Name Else DoCmd.OpenReportobj1.Name,acViewPreview CheckMailItTagobj1.Name DoCmd.CloseacReport,obj1.Name,acSaveNo EndIf Nextobj1     EndSub      'OpenOutlookbeforeinvokingthisprocedure SubCheckMailItTag(obj1name) Dimrep1AsReport     'SetreferencetoReportsmembercorresponding 'toAllReportsmember Setrep1=Reports(obj1name)     'IfTagpropertysays "mailit", 'createasnapshotfileandmailit Ifrep1.Tag= "mailit" Then DoCmd.SendObjectacOutputReport,obj1name,_ acFormatSNP, "virginia@cabinc.net",,,_  "SnapshotReport", "Hereisthereport.",False EndIf EndSub 



Programming Microsoft Office Access 2003
Programming MicrosoftВ® Office Access 2003 (Core Reference) (Pro-Developer)
ISBN: 0735619425
EAN: 2147483647
Year: 2006
Pages: 144
Authors: Rick Dobson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net