Chapter 9: Creating Reports and Web-Enabled Output

Chapter 8 explored techniques to consume information from web services and the SharePoint product family. This chapter covers:

• Creating reports programmatically and how to output data from Access databases in ways that can be used over the Web. For example, you will explore how to set the record source for a report dynamically, so you can use the same report across different data sources.

• Using the OutputTo method to export data to various formats, such as HTML.

Working with Reports from VBA

Reports can be manipulated in numerous ways from VBA code. For example, you can change the data source on which the report is based from within VBA code. You can also create reports from scratch or modify existing reports from within VBA code. Let’s look at a few examples to further illustrate these concepts.

Tip 

Many of the examples in this chapter that deal with manipulating existing reports use the Northwind database. If you want to create those particular examples yourself, open the Northwind database and add a new module. Only the code for the Try It Out examples is included in the sample code available on Wrox.com for this chapter. If you do not have the Northwind database installed, you can install it and the other sample databases from the Microsoft Access setup program, or you can download it from Microsoft’s web site in the Microsoft Office section that contains Access sample databases.

Setting the Report Data Source

The DataSource property of a Report object can be specified as a SQL statement. Suppose that you have an existing report called Alphabetical List of Products. The following procedure illustrates one way you can modify the DataSource of the report:

  Sub ModifyExistingReport() 'declare a variable to store the SQL string Dim strSQL As String 'set the SQL statement value to show only the top 5 orders strSQL = "SELECT DISTINCTROW TOP 5 Orders.[Order ID], Orders.[Order Date]," & _          "[Order Subtotals].Subtotal AS SaleAmount, " & _          "[Customers Extended].Company AS CompanyName, Orders.[Shipped Date] " & _          "FROM [Customers Extended] " & _          "INNER JOIN (Orders INNER JOIN [Order Subtotals] ON " & _          "Orders.[Order ID] = [Order Subtotals].[Order ID]) " & _          "ON [Customers Extended].ID = Orders.[Customer ID] " & _          "ORDER BY [Order Subtotals].Subtotal DESC;" 'access the report's design (hidden from the user) DoCmd.Echo False DoCmd.OpenReport "Top Ten Biggest Orders", acViewDesign 'set the source of the report to the SQL statement Reports("Top Ten Biggest Orders").RecordSource = strSQL 'close the report's design and save changes DoCmd.Close , , acSaveYes 'now open the report for the user in preview mode DoCmd.OpenReport "Top Ten Biggest Orders", acViewPreview DoCmd.Echo True End Sub 

Notice first how a SQL statement is created to specify which fields the report should be based upon. The report’s design view is then accessed in hidden mode (DoCmd.Echo False) so that the user does not see the modifications. The RecordSource is then assigned to the strSQL variable. The report is then closed and the changes saved. Finally, the report is opened in preview mode, so you can see the changes.

Important 

When you make changes to report properties (such as the RecordSource property), you actually permanently change the report to those settings, just as if you had done so in the designer. You don’t just make a temporary change that is undone after the code executes.

You can also specify an ADO recordset as a report’s data source. You could modify the prior example as follows:

  Sub ModifyExistingReport() 'declare a variable to store the SQL string Dim strSQL As String 'set the SQL statement value to show only the top 5 orders strSQL = "SELECT DISTINCTROW TOP 5 Orders.[Order ID], Orders.[Order Date]," & _          "[Order Subtotals].Subtotal AS SaleAmount, " & _          "[Customers Extended].Company AS CompanyName, Orders.[Shipped Date] " & _          "FROM [Customers Extended] " & _          "INNER JOIN (Orders INNER JOIN [Order Subtotals] ON " & _          "Orders.[Order ID] = [Order Subtotals].[Order ID]) " & _          "ON [Customers Extended].ID = Orders.[Customer ID] " & _          "ORDER BY [Order Subtotals].Subtotal DESC;" 'declare and instantiate a new recordset Dim rsDiscontinued As ADODB.Recordset Set rsDiscontinued = New ADODB.Recordset 'open the recordset based on the SQL statement rsDiscontinued.Open strSQL, CurrentProject.Connection 'access the report's design (hidden from the user) DoCmd.Echo False DoCmd.OpenReport "Top Ten Biggest Orders", acViewDesign 'set the source of the report to the recordset source Reports("Top Ten Biggest Orders").RecordSource = rsDiscontinued.Source 'close the report's design and save changes DoCmd.Close , , acSaveYes 'now open the report for the user in preview mode DoCmd.OpenReport "Top Ten Biggest Orders", acViewPreview DoCmd.Echo True 'close the recordset and free the memory rsDiscontinued.Close Set rsDiscontinued = Nothing End Sub 

Notice how this example uses the SQL statement to create the ADO recordset and then assigns the RecordSource property of the report to the recordset’s Source property.

Creating Reports Programmatically

Just as you can work with forms and other Access objects programmatically, you can also work with reports programmatically. You can create reports from scratch and modify existing reports. Let’s look at how to create a report from scratch.

Creating an Empty Report

To create a new report, you use the CreateReport method. Here is the basic syntax:

  Dim rptReport as Access.Report Set rptReport = CreateReport 

Below is an example that creates a new empty report and deletes any existing report with the same name before creating the new report:

  Sub CreateNewReport() Dim rptCustomers As Access.Report Dim strReportName As String Dim aoAccessObj As AccessObject 'set the name of the new report strReportName = "Customers" 'delete any existing report with that name For Each aoAccessObj In CurrentProject.AllReports     If aoAccessObj.Name = strReportName Then         DoCmd.DeleteObject acReport, strReportName     End If  Next aoAccessObj 'create a new Customers Report Set rptCustomers = CreateReport 'save and close the new report DoCmd.Save , strReportName DoCmd.Close End Sub 

First, the code loops through the AllReports collection to delete any existing report with the same name. Then, the Access report object rptCustomers is assigned to the CreateReport method. The report is then saved with the name specified. At this point, the report is empty and contains no controls. Let’s look at how to add controls to the report.

Adding Controls to the Report

The CreateReportControl method allows you to add new controls to a report. The CreateReport Control method has the following basic syntax:

  Set Object = CreateReportControl(ReportName, ControlType, Section, Parent, ColumnName, Left,  Top, Width, Height) 

This procedure is called with parentheses because a value, which in this case is a new control, is returned. Now, let’s modify the prior code to add four fields to the report: CompanyName, ContactName, Title, and Phone. Each field needs a corresponding text box and label. The following modified procedure is one way to accomplish this:

  Sub CreateNewReport()   Dim rptCustomers As Access.Report Dim strReportName As String   Dim aoAccessObj As AccessObject   Dim txtTextBox As Access.TextBox   Dim lblLabel As Access.Label   Dim strSQL As String   Dim intPosition As Integer   'set the name of the new report   strReportName = "Customers"   'set the source of the report to this SQL   strSQL = "SELECT Customers.Company, " & _   "Customers.[First Name] & ' ' & Customers.[Last Name] AS ContactName, " & _   "Customers.[Job Title], Customers.[Business Phone] FROM Customers;"   'delete any existing report with that name   For Each aoAccessObj In CurrentProject.AllReports       If aoAccessObj.Name = strReportName Then          DoCmd.DeleteObject acReport, strReportName       End If   Next aoAccessObj   'create a new Customers Report and set properties   Set rptCustomers = CreateReport   'set the report record source to the SQL Statement   rptCustomers.RecordSource = strSQL   'set the height, caption, and other report options   rptCustomers.Section("Detail").Height = 500   rptCustomers.Caption = "Client Contact List"   'add a Company Name label and text box to the report   intPosition = 0   Set txtTextBox = CreateReportControl(rptCustomers.Name, acTextBox, _                    acDetail, , "Company", intPosition)   txtTextBox.Name = "txtCompanyName"   txtTextBox.Width = 1800   Set lblLabel = CreateReportControl(rptCustomers.Name, acLabel, _                acPageHeader, , , intPosition) lblLabel.Name = "lblCompanyName" lblLabel.Caption = "Company Name" lblLabel.Height = txtTextBox.Height lblLabel.Width = txtTextBox.Width lblLabel.FontBold = True 'add a Contact Name label and text box to the report intPosition = txtTextBox.Width + txtTextBox.Left + 350 Set txtTextBox = CreateReportControl(rptCustomers.Name, acTextBox, _                     acDetail, , "ContactName", intPosition) txtTextBox.Name = "txtContactName" txtTextBox.Width = 1800 Set lblLabel = CreateReportControl(rptCustomers.Name, acLabel, _                    acPageHeader, , , intPosition) lblLabel.Name = "lblContactName" lblLabel.Caption = "Contact Name" lblLabel.Height = txtTextBox.Height lblLabel.Width = txtTextBox.Width lblLabel.FontBold = True 'add a Contact Title label and text box to the report intPosition = txtTextBox.Width + txtTextBox.Left + 500 Set txtTextBox = CreateReportControl(rptCustomers.Name, acTextBox, _                   acDetail, , "Job Title", intPosition) txtTextBox.Name = "txtTitle" txtTextBox.Width = 1800 Set lblLabel = CreateReportControl(rptCustomers.Name, acLabel, _                  acPageHeader, , , intPosition) lblLabel.Name = "lblTitle" lblLabel.Caption = "Title" lblLabel.Height = txtTextBox.Height lblLabel.Width = txtTextBox.Width lblLabel.FontBold = True 'add a Contact Phone label and text box to the report intPosition = txtTextBox.Width + txtTextBox.Left + 1000 Set txtTextBox = CreateReportControl(rptCustomers.Name, acTextBox, _                     acDetail, , "Business Phone", intPosition) txtTextBox.Name = "txtPhone" Set lblLabel = CreateReportControl(rptCustomers.Name, acLabel, _                    acPageHeader, , , intPosition)   lblLabel.Name = "lblPhone"   lblLabel.Caption = "Phone"   lblLabel.Height = txtTextBox.Height   lblLabel.Width = txtTextBox.Width   lblLabel.FontBold = True   'save and close the new report DoCmd.Save , strReportName DoCmd.Close End Sub 

In the code example shown again in the following section, notice how a text box and label control are declared and then used to create each of the fields on the report. For example, txtTextBox is assigned to the result of the CreateReportControl method, which specifies the report name, type of control, section to add the control, name of the control, and the position in which to place the control. Various other properties of the text box control are specified, such as the Name and Width properties. The corresponding label control is also created and various settings specified.

 'add a Company Name label and text box to the report intPosition = 0 Set txtTextBox = CreateReportControl(rptCustomers.Name, acTextBox, _                   acDetail, , "Company", intPosition) txtTextBox.Name = "txtCompanyName" txtTextBox.Width = 1800 Set lblLabel = CreateReportControl(rptCustomers.Name, acLabel, _                 acPageHeader, , , intPosition) lblLabel.Name = "lblCompanyName" lblLabel.Caption = "Company Name" lblLabel.Height = txtTextBox.Height lblLabel.Width = txtTextBox.Width lblLabel.FontBold = True

The same basic steps are then repeated to create each control for the report.

Try It Out-Creating a Report Programmatically

Now it’s your turn to create a report programmatically. This example creates a report based on a new table that you create and uses a SQL statement as the report’s RecordSource property.

1. Create a new database by selecting the Office Button New. From the list of available templates, select Blank Database. Specify Ch9CodeExamples as the file name and path, and press the Create button.

2. Add a new tblComplaints table to the database, as shown in Figure 9-1.

Figure 9-1

3. Open the table and add some records, such as those shown in Figure 9-2.

   
   Figure 9-2

4. Insert a new module into the database. To do so, choose the Create ribbon, click on Macro, and select Module.

5. Add the following CreateComplaintsReport procedure to the module:

     Sub CreateComplaintsReport() Dim rptComplaints As Access.Report Dim strReportName As String Dim txtTextBox As Access.TextBox Dim lblLabel As Access.Label Dim strSQL As String Dim intPosition As Integer 'set the name of the new report strReportName = "Unresolved Customer Complaints" strSQL = "SELECT * FROM tblComplaints WHERE Resolved=False" 'create a new Unresolved Customer Complaints Report Set rptComplaints = CreateReport With rptComplaints     'set the report record source to the SQL Statement     .RecordSource = strSQL     'set the height, caption, and other report options     .Section("Detail").Height = 500     .Caption = "Unresolved Customer Complaints" End With 'add a Customer Name label and text box to the report intPosition = 0 Set txtTextBox = CreateReportControl(rptComplaints.Name, acTextBox, _                     acDetail, , "CustomerName", intPosition) txtTextBox.Name = "txtCustomerName" txtTextBox.Width = 1800 Set lblLabel = CreateReportControl(rptComplaints.Name, acLabel,    _                  acPageHeader, , , intPosition) lblLabel.Name = "lblCustomerName" lblLabel.Caption = "Customer Name" lblLabel.Height = txtTextBox.Height lblLabel.Width = txtTextBox.Width lblLabel.FontBold = True 'add a Customer Day Phone label and text box to the report intPosition = txtTextBox.Width + txtTextBox.Left + 350 Set txtTextBox = CreateReportControl(rptComplaints.Name, acTextBox, _                   acDetail, , "CustomerDayPhone", intPosition) txtTextBox.Name = "txtCustomerDayPhone" txtTextBox.Width = 1800 Set lblLabel = CreateReportControl(rptComplaints.Name, acLabel, _                 acPageHeader, , , intPosition) lblLabel.Name = "lblCustomerDayPhone" lblLabel.Caption = "Day Phone" lblLabel.Height = txtTextBox.Height lblLabel.Width = txtTextBox.Width lblLabel.FontBold = True 'add a Customer Evening Phone label and text box to the report intPosition = txtTextBox.Width + txtTextBox.Left + 500 Set txtTextBox = CreateReportControl(rptComplaints.Name, acTextBox,  _                  acDetail, , "CustomerEveningPhone", intPosition) txtTextBox.Name = "txtCustomerEveningPhone" txtTextBox.Width = 1800 Set lblLabel = CreateReportControl(rptComplaints.Name, acLabel, _                  acPageHeader, , , intPosition) lblLabel.Name = "lblCustomerEveningPhone" lblLabel.Caption = "Evening Phone" lblLabel.Height = txtTextBox.Height lblLabel.Width = txtTextBox.Width lblLabel.FontBold = True 'add an Issue Description label and text box to the report intPosition = txtTextBox.Width + txtTextBox.Left + 1000 Set txtTextBox = CreateReportControl(rptComplaints.Name, acTextBox, _                   acDetail, , "IssueDescription", intPosition) txtTextBox.Name = "txtIssueDescription" txtTextBox.Width = 2000 txtTextBox.Height = 750 Set lblLabel = CreateReportControl(rptComplaints.Name, acLabel, _                acPageHeader, , , intPosition) lblLabel.Name = "lblIssueDescription" lblLabel.Caption = "Issue Description" lblLabel.Height = txtTextBox.Height lblLabel.Width = txtTextBox.Width lblLabel.FontBold = True 'save and close the new report DoCmd.Save , strReportName DoCmd.Close End Sub 

6. Run the procedure from the Immediate Window by typing the procedure name.

How It Works

First, you created a new database and a table called tblComplaints. You then added some sample data to the tblComplaints table so the report would have data to display. Next, you created a new module and added the CreateComplaintsReport procedure to the module.

 Sub CreateComplaintsReport()

The procedure declares some variables for use in the procedure, such as a Report object for creating the new report and the text box and label controls for adding to the report:

 Dim rptComplaints As Access.Report Dim strReportName As String Dim txtTextBox As Access.TextBox Dim lblLabel As Access.Label Dim strSQL As String Dim intPosition As Integer 'set the name of the new report

 strReportName = "Unresolved Customer Complaints" strSQL = "SELECT * FROM tblComplaints WHERE Resolved=False"

The new report was created using the CreateReport method, and properties such as RecordSource, Height, and Caption were specified. The RecordSource property assigned the source of data to the SQL statement. The Detail section’s Height property specified the size of the report’s detail section.

 'create a new Unresolved Customer Complaints Report Set rptComplaints = CreateReport With rptComplaints     'set the report record source to the SQL Statement     .RecordSource = strSQL     'set the height, caption, and other report options     .Section("Detail").Height = 500     .Caption = "Unresolved Customer Complaints" End With

Next, you added code to create the report controls. For example, to create a new Customer Name label and text box, you used the CreateReportControl method. The parameters of CreateReportControl specified, among other things, where to include the field (in the report or detail section), what data source field the new field should be based on, and what position it should start in.

 'add a Customer Name label and text box to the report intPosition = 0 Set txtTextBox = CreateReportControl(rptComplaints.Name, acTextBox, _                  acDetail, , "CustomerName", intPosition) txtTextBox.Name = "txtCustomerName" txtTextBox.Width = 1800 Set lblLabel = CreateReportControl(rptComplaints.Name, acLabel, _                acPageHeader, , , intPosition) lblLabel.Name = "lblCustomerName" lblLabel.Caption = "Customer Name" lblLabel.Height = txtTextBox.Height lblLabel.Width = txtTextBox.Width lblLabel.FontBold = True

The same basic code was repeated for each of the remaining controls on the report, namely the labels and text boxes for Customer Day Phone, Customer Evening Phone, and Issue Description. The final lines of the procedure saved and closed the report.

 'save and close the new report DoCmd.Save , strReportName DoCmd.Close End Sub

When you ran the new procedure, the report was created and appeared in the Database Window, as shown in Figure 9-3. The report was then opened in preview mode, as shown in Figure 9-4, so you could see the final result of your efforts.

Figure 9-3

Figure 9-4

Using the Printer Object to Designate Printing Options

In Chapter 3, you learned about the Printers collection and Printer object. This collection and object are again important if you want to modify the margins or other printer characteristics of a report. For example, if you wanted to modify the margins and number of copies for the first printer, you could use code similar to the following:

  Dim prtPrinter As Printer Set prtPrinter = Application.Printers(0) prtPrinter.TopMargin = 500 prtPrinter.BottomMargin = 250 prtPrinter.LeftMargin = 500 prtPrinter.RightMargin = 500 prtPrinter.Copies = 5 

The example shown above for setting printer margins and copies is just a simple example for illustration purposes. In order to follow good programming practices, you would typically want to avoid hard-coding such values into the code. You would instead allow these values to be set by a user in a user interface or in a settings file and then set the values in the code above to the user-specified settings.

As another printer example, you could use the AllPrinters collection to loop through all printers on the computer and take some particular action, such as displaying the printer name in a printer dialog box. See Chapter 3 for additional examples.

Working with Report Events

At this point, you should be very familiar with how to add events to forms, such as the Click event of a button. Reports have events just as forms do, but not as many, as you can see in Figure 9-5.

Figure 9-5

You can also write VBA code behind report events, such as Open, Close, Activate, and Deactivate. For example, you might want to display a message to users when the report opens to inform them that the report will take a long time to load. You may want to add a custom toolbar to be displayed from the Activate event and then remove that toolbar when the Deactivate event fires.

The following is an example of adding and removing a custom toolbar from the report’s Activate and Deactivate events.

  Private Sub Report_Activate()   DoCmd.ShowToolbar "CustomComplaints", acToolbarYes End Sub Private Sub Report_Deactivate()   DoCmd.ShowToolbar "CustomComplaints", acToolbarNo End Sub 

Now that you have a basic idea of how to create reports programmatically and how to create report events, you can move on to the topic of exporting Access data to various Web formats.