Creating Reports Programmatically

Because Access has such a powerful user interface for designing and manipulating reports, you probably will not find it convenient to create reports programmatically. However, if your application requires you to create a wizard that allows users to design custom reports, this section shows you how to get started. In addition, if you ever need to modify the controls on a large number of existing reports, this section provides you with useful background on the subject. Later sections in the chapter provide additional sample code that demonstrates how to manipulate existing reports programmatically.

Laying out a basic tabular report is a relatively simple task that requires just two methods : CreateReport and CreateReportControl . The Access Application object's CreateReport method creates a Report object. After you create a report, you need to assign a record source to it and populate the report with controls that display the contents of the record source. The Access Application object's CreateReportControl method adds controls to a report.

Creating and Saving an Unnamed Empty Report

Invoking the CreateReport method by itself creates an empty report. This method belongs to the Application object, which is the top level Access object. As a consequence, you can specify the CreateReport method without designating its object, which is the Application object. Unless you change the Report template setting, the CreateReport method generates an empty report with three sections: a Page Header, a Detail section, and a Page Footer. When you create a report with this method, that report will not exist in any Access database file or Access project. Access automatically assigns the report a name , such as Report1 . If the application's file already has a report named Report1 , Access names the report Report2 , and so on.

The first programming sample in this section illustrates the syntax for creating an empty report with the default name. If you execute this procedure twice in a file that has no existing reports named Reportx (where x is a number), the procedure creates two reports: Report1 and Report2 . You can open either report in Design view and verify its empty status.

 SubCreateDefaultNamedReport()  Dimrpt1AsReport     'Createandsaveanemptyreport 'withadefaultname Setrpt1=CreateReport DoCmd.Close,,acSaveYes     EndSub

Creating and Saving a Report with a Custom Name

The preceding sample generates reports named Report1 , Report2 , and so on with the CreateReport method because the method does not provide a means to name a report. Furthermore, the DoCmd object's Close method does not let you close a report and give it a custom name if the report does not already exist. The acSaveYes argument for the DoCmd object's Close method applies only to existing objects. In essence, this method lets you save over an existing object rather than save an object for the first time.

The DoCmd object's Save method can save a new object with a custom name. However, this method does not replace an existing object with a new object. Therefore, the trick to saving reports with a custom name is to use a two-step process. First, if the report already exists, you need to delete it. Once you have removed any prior versions of a report that have the custom name you want to use, you can perform the second step: using the DoCmd object's Save method to save the report. As long as you delete any prior versions of the report before invoking this method, it will always work correctly.

The following sample illustrates saving an empty report with a custom name. It relies on three procedures. The first procedure, CallCreateCustomNamedReport , performs two tasks .

It assigns a value to a string variable, which is the custom name for the report.
It passes the string argument to the CreateCustomNamedReport procedure.

After creating a new report, the second procedure conditionally deletes any existing report that has the custom name passed to it. The code calls the DoesReportExist function procedure to assess this. If a report with the custom name already exists, the DoesReportExist procedure returns a Boolean value of True . When the function returns a value of True , the CreateCustomNamedReport procedure invokes the DeleteObject method of the DoCmd object for the prior version of the report. After ensuring the deletion of the report's prior version, the procedure can invoke the DoCmd object's Save method without the risk of generating a run-time error by saving over an object that already exists.

The DoesReportExist function procedure passes through all the members of the AllReports collection for the CurrentProject object. Recall from Chapter 3 that members of the Access Allxxx collections are AccessObject objects. (Later in this chapter, the section "Manipulating Reports and Report Controls" deals more fully with the AllReports collection.) The syntax in the DoesReportExist function procedure is appropriate for either an Access database file or an Access project.

Note	The AllReports collection contains the names of all reports, regardless of whether they are open or closed. The Reports collection is not useful for detecting whether a report already exists because this collection contains only open reports.

 SubCallCreateCustomNamedReport() Dimstr1AsString     str1= "rpt1" CreateCustomNamedReportstr1     EndSub     SubCreateCustomNamedReport(str1AsString) Dimrpt1AsAccess.Report     'Createareport Setrpt1=CreateReport     'Ifthenameforyournewreportalreadyexists, 'deletetheoldversionbeforesavingthenewversion IfDoesReportExist(str1)Then DoCmd.DeleteObjectacReport,str1 EndIf DoCmd.Save,str1 DoCmd.Close     EndSub     FunctionDoesReportExist(str1AsString)AsBoolean DimaroAsAccessObject     'SearchthemembersoftheAllReportscollection 'forareportwiththenameinstr1;returnTrue 'ifreportexistsandFalseotherwise ForEacharoInCurrentProject.AllReports Ifaro.Name=str1Then DoesReportExist=True ExitFunction EndIf Nextaro     EndFunction

Programmatically Creating a Report with Controls

As you can see, adding a custom report is not very complicated; the syntax for calling the CreateReport method is straightforward. Access Help documents two arguments for this method, but you do not need either of them unless you are using a nonstandard report template.

To add controls to a report created with the CreateReport method, you need to invoke the Application object's CreateControlReport method. The CreateControlReport method can take as many as nine arguments, but Access requires only two. You will usually need to specify a handful of arguments. Just like the CreateReport method, the CreateControlReport method returns an object ”in this case, a control for a report.

The required arguments for the CreateReportControl method include the report name that will contain the control and the type of control to be added. You can specify the control type with an intrinsic constant ”for example, specify acTextBox for a text box control. Listing the members of the AcControlType in the Object Browser presents all the intrinsic constants for control types; reports and forms share the same control types. Although specifying a report section for the control is not mandatory (the Detail section is the default), you will normally specify the destination section for the report control. Any bound control, such as a text box, will require a ColumnName argument. This argument is equivalent to the ControlSource property for the control. Your ColumnName argument indicates the name of a column from the report's RecordSource property setting.

You often have to designate the position and size of controls. Parameters for these control properties can be set either in the CreateReportControl argument list or with separate property assignments. Both the method arguments and object properties use the same names to refer to these control settings. The Top and Left arguments indicate the offset of the control from the top-left corner of the report section in which the control resides. The Width and Height arguments denote a control's size. When designating the Top , Left , Width , or Height arguments in Microsoft Visual Basic for Applications (VBA), you should specify units in twips. (Recall that 1,440 twips equals 1 inch.)

A Height setting is necessary when working with label controls that serve as column headings in tabular report layouts. You can make this setting with an argument when you call the CreateReportControl method or by using a separate assignment statement.

The following code sample creates a report based on the Customers table in the Northwind database. The report shows four columns in a format that provides a basic telephone directory of customer contacts. Figure 6-1 shows an excerpt from the report's layout. Notice that the report's design distinguishes the column headers from the data rows by assigning them a bold font.

Figure 6.1: A report created programmatically based on the CreateReport and CreateReportControl methods.

The code for the report in Figure 6-1 has several sections. It begins by creating a report with the CreateReport method. Then, it assigns a SQL string to the RecordSource property of the report. Without this assignment, you would have to specify the record source for a report at run time before the report opens. Immediately after the record source assignment, the procedure specifies the height of the Details section as about a quarter of an inch. This height is needed to control the spacing between rows in the table. As with other size assignments for controls through VBA, the units are twips. Next, the procedure adds the controls for the four columns. After adding controls to display and label data, the procedure saves the report with a custom name ( rpt2 ). This name appears in Figure 6-1 in the title bar. The closing segment of the procedure invokes the DoesReportExist function described earlier in the section.

Four blocks of code successively add each column to the report. A comment that mentions the data for the column precedes each block. Within each block, the CreateReportControl adds two controls ”a text box and a label.

The CreateReportControl method for the text box in the first column has four arguments. These specify the report name, the type of control, the report section for the control, and the column name from the report's record source. This last argument sets the ControlSource property for the text box. The CreateReportControl method does not have an argument for naming the control, so the procedure assigns a string to the control's Name property immediately after the invocation of the CreateReportControl method. The second invocation of the CreateReportControl method for the CustomerID column creates the column header. The arguments for this control designate a label control type that resides in the report's Page Header section. In addition to assigning a name to the control, the property assignments for the label control specify Caption , Width , and Height properties. You must always set these label control properties. The code block closes by assigning a bold font setting to the label.

The blocks of code that create controls for the report's second through fourth columns follow the example of the first column ”with one exception. The first block positions its label and text box controls flush with the report's left edge (which is the current printer's left margin setting). The second through fourth columns offset the left edge of their controls by the left edge of the text box for the preceding column plus the width of that text box plus 50 twips. The wid1 memory variable is used to calculate this spacing by adding the prior column's Width setting to the prior Left setting. The sixth argument for the CreateReportControl method adds 50 as an additional offset to wid1 when specifying the left edge of controls in the second through the fourth columns.

Note

Access 2002 introduced a new Printer object that is included in Access 2003. Printer objects are members of the Printers collection. Use members of this collection when you want to designate one printer from a choice of multiple printers to which an application can connect. With the Printer object, you can control settings such as the left margin, the number of copies, and the paper bin. See Access Help, Printer Object for more detailed information, including code samples.

There is one additional setting you can use when adding the controls for the columns. By default, the control width for text boxes is 1 inch (1,440 twips). This setting is appropriate for all columns in the report, except for the second column, which needs a width of 2,700 twips to accommodate longer customer names.

 SubCreateAReportWithASource()  OnErrorGoToReportSource_Trap DimrptAsReport Dimtxt1AsAccess.TextBox Dimlbl1AsAccess.Label Dimstr1AsString Dimwid1AsInteger 'CreateareportwithaRecordSourceassignment Setrpt1=CreateReport rpt1.RecordSource= "SELECT*FROMCustomers" rpt1.Section("Detail").Height=365     'AddpagecolumnlabelandtextboxforCustomerID Settxt1=CreateReportControl(rpt1.Name,acTextBox,_ acDetail,, "CustomerID") txt1.Name= "txtCustomerID" Setlbl1=CreateReportControl(rpt1.Name,acLabel,_ acPageHeader) lbl1.Name= "lblCustomerID" lbl1.Caption= "CustomerID" lbl1.Width=txt1.Width lbl1.Height=txt1.Height lbl1.FontBold=True     'AddpagecolumnlabelandtextboxforCompanyName. 'Setthewidthofthiscolumnat2700twips,whichis 'differentthanthedefaultsettingof1440twips. wid1=txt1.Width Settxt1=CreateReportControl(rpt1.Name,acTextBox,_ acDetail,, "CompanyName",wid1+50) txt1.Name= "txtCompany" txt1.Width=2700 Setlbl1=CreateReportControl(rpt1.Name,acLabel,_ acPageHeader,,,wid1+50) lbl1.Name= "lblCompanyName" lbl1.Caption= "CompanyName" lbl1.Width=2700 lbl1.Height=txt1.Height lbl1.FontBold=True     'AddpagecolumnlabelandtextboxforContactName wid1=txt1.Left+txt1.Width Settxt1=CreateReportControl(rpt1.Name,acTextBox,_ acDetail,, "ContactName",wid1+50) txt1.Name= "txtContactName" Setlbl1=CreateReportControl(rpt1.Name,acLabel,_ acPageHeader,,,wid1+50) lbl1.Name= "lblContactName" lbl1.Caption= "ContactName" lbl1.Width=txt1.Width lbl1.Height=txt1.Height lbl1.FontBold=True     'AddpagecolumnlabelandtextboxforPhone wid1=txt1.Left+txt1.Width Settxt1=CreateReportControl(rpt1.Name,acTextBox,_ acDetail,, "Phone",wid1+50) txt1.Name= "txtPhone" Setlbl1=CreateReportControl(rpt1.Name,acLabel,_ acPageHeader,,,wid1+50) lbl1.Name= "lblPhone" lbl1.Caption= "Phone" lbl1.Width=txt1.Width lbl1.Height=txt1.Height lbl1.FontBold=True     'Ifthenameforyournewreportalreadyexists, 'deletetheoldversionbeforesavingthenewversion str1= "rpt2" IfDoesReportExist(str1)Then DoCmd.DeleteObjectacReport,str1 EndIf DoCmd.Save,str1 DoCmd.Close     ReportSource_Exit: 'Cleanupobjects Setrpt=Nothing ExitSub     ReportSource_Trap: IfErr.Number=2008Then 'Ifthestr1reportisalreadyopen, 'closeitsothattheprocedurecandeleteit DoCmd.CloseacReport,str1 Resume Else Debug.PrintErr.Number,Err.Description MsgBox "Programabortedforunanticipatedreasons.",_ vbCritical, "ProgrammingMicrosoftAccess2003" EndIf     EndSub