لطفا منتظر باشید ...
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 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.
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
