Team LiB
Previous Section Next Section

Dynamically Updating Reports

In addition to revising the format of a report dynamically, you can devise solutions that let users dynamically specify the contents of a report. Use the RecordSource property of a report to set its records dynamically. Unlike Access forms, Access reports do not expose a Recordset property for Access database files. Therefore, you cannot dynamically assign an ADO recordset as the source for a report in these files. However, this section describes a workaround you can use.

Note 

In contrast to Access database files, Access projects—which are used for SQL Server and MSDE (Microsoft SQL Server 2000 Desktop Engine) databases—do expose a Recordset property for their reports. Chapter 12 covers the use of reports in Access projects.

Programming the RecordSource Property

The RecordSource property takes a string value as a setting. Therefore, you can design a SQL string that specifies a record source for the report based on user input. This string can reference any local tables and queries, as well as any linked record sources from other databases. Your code can update the report's title to reflect the new content.

Figure 6-27 shows a form and a report. Users can manipulate the form to change the content for the report. The form includes a text box, an option group of five check boxes, and a command button. After entering a number in the text box and selecting a Price Comparison Operator, the user can click the command button to open the report shown on the right in Design view. Recall that this view is necessary to persist changes to the property settings for the form and its controls. The sample code in this case assigns a new Record Source property and updates the Caption property for the label that displays the report title. After programmatically updating the two report properties, the application opens the results in Preview mode to display the results of the new record source.

Click To expand
Figure 6.27: In the form on the left, users can designate a record source and a corresponding title for the report on the right.

The cmdPrintThem_Click event procedure (shown in the next code listing) for the command button in the form that appears in Figure 6-27 performs three tasks. First, it constructs a SQL string based on selections made in the form. Some default values are generated if the user doesn't set the controls before clicking the button. The procedure successively adds clauses to a starter statement that lists product name and unit price from the Products table. The procedure initially appends a WHERE clause to the core statement based on the selected check box and the quantity entered in the text box. After adding the WHERE clause, the procedure appends an ORDER BY clause that sorts the return set from the SQL string by unit price. If a user selects the greater than operator (>) or the greater than or equal to operator (>=), the procedure specifies a descending sort order. Otherwise, the return set sorts by unit price in the default ascending order.

The second component of the cmdPrintThem_Click procedure programmatically revises the record source setting and caption for a label on the report. After opening the report in Design view, the code executes a With…End With statement based on the report. To eliminate screen clutter, the second component invokes the Echo method with an argument of False. This suppresses screen updates until a subsequent statement invokes the Echo method with an argument of True. Inside the With…End With block, the procedure sets the report's RecordSource property to the SQL string computed from the first part of the procedure. Then, the procedure changes the caption for the label that displays the report's title. A string expression that draws on the option group value and the amount in the text box facilitates this task.

Note 

After making the changes in the second component, the procedure closes the report and saves the revisions. In some earlier versions of Access, it wasn't necessary to commit the changes in order to preview them. However, Access 2003 requires that you explicitly save the changes in Design view before you can view them in Preview mode.

The third component performs two functions. It opens the report in Preview mode so that users can see it. Then, the final command restores the Echo function, which displays the report. This technique of turning off and then restoring the Echo effect leads to crisp screen transitions.

Private Sub cmdPrintThem_Click()
Dim str1 As String, strOperator As String
Dim strWhere As String
   
'Set up SQL statement for report record source
    str1 = "Select ProductName, UnitPrice " & _
        "from Products"
    If IsNull(optRule) Then optRule = 1
    If IsNull(txtAmount) Then txtAmount = 0
    strOperator = Choose(optRule, ">", ">=", "=", "<=", "<")
    strWhere = "Where UnitPrice" & strOperator & txtAmount
    str1 = str1 & " " & strWhere & " Order By UnitPrice"
    If optRule <= 2 Then
        str1 = str1 & " Desc"
    End If
'The commented Debug.Print statement is convenient for debugging
'your SQL statement; remove the comment when you change the
'SQL statement construction
'   Debug.Print str1
    
'Open report in Design view to set the report's record source
'and its label's caption
    DoCmd.Echo False
    DoCmd.OpenReport "rptProductsfromForm", acViewDesign
    With Reports("rptProductsfromForm")
        .RecordSource = str1
        .Controls("lblTitle").Caption = _
            "Products with a Unit Price " & strOperator & _
            " $" & txtAmount
    End With
    DoCmd.Close , , acSaveYes
   
'Now show the form to the user
    DoCmd.OpenReport "rptProductsfromForm", acViewPreview
    DoCmd.Echo True
   
End Sub

Using an ADO Recordset as the Report's Record Source

Recall that reports, unlike forms, do not expose a Recordset property within Access database files. Therefore, if you have an ADO recordset you're using for some other purpose, you cannot simply assign it to the Recordset property of a report. However, you can return the SQL statement for the data source of a recordset. Use the recordset's Source property to return its SQL statement. Then, you can assign the SQL statement that represents the source for the ADO recordset to the RecordSource property for a report. The connection from the report to the records does not actually pass through the recordset. However, an Access report can emulate the Recordset property for a form through this technique.

The next sample demonstrates how to use the records in an ADO recordset as the source for a report. By using an InputBox function, the sample lets a user dynamically select a subset of rows from the Customers table in the Northwind database as the source for a recordset. Next, the sample loops through the members of the AllTables collection to determine whether the Access database file contains a link to the data source for the recordset. If the database file already contains a link, the report can use the SQL statement that serves as the Source property for the recordset. If no link to the remote data source for the recordset exists, the procedure creates a link to the data source for the recordset's connection.

The sample procedure starts by instantiating and opening a connection to the Northwind database. Any database source will work, but you might have to change the method for linking the database. This sample demonstrates linking to an Access database file. (Chapter 1 includes a code sample for linking to any ODBC-compliant data source.) Next, the procedure prompts for the first letter of a customer ID. The rst1 recordset uses this criterion value to select only those customer IDs with the specified first letter.

After opening the recordset, the procedure prepares to start using the excerpt from the Customers table as the source for a report of mailing labels. The Chapter06.mdb file contains the layout for the report saved in rptMailingLabels.

Before transferring the Source property from the recordset to the RecordSource property for the rptMailingLabels report, the procedure takes a couple of preliminary steps. First, the procedure lets the user know that it might take a while to compile the data for the report, and it temporarily turns off screen updating. Second, the procedure relies on the IsLinked function to search for a table named Customers in the current database. If the linked table does not exist, the procedure links to the Customers table in the Northwind database. Otherwise, the procedure uses the existing linked table. In any event, the procedure uses a Boolean memory variable (bol1) to note whether the linked table existed previously.

Note 

This sample assumes no conflicts exist between the names in the current database and those in the database to which you're connecting. You will typically control the names of the tables in the local database and sometimes even in the linked database.

At this point in the procedure, a link to the Customers table definitely exists in the Northwind database. Therefore, the sample assigns the recordset's Source property to the report's RecordSource property. After saving the change made in Design view to the report, the procedure opens the report in Preview mode and restores screen updating. This last step is necessary to view the report. As the procedure closes, it performs the usual object cleanup and checks whether it can remove the link to the Customers table in the Northwind database. If the link was added exclusively for the report, the procedure can delete the link; otherwise, it will not remove the link.

Sub ReportBasedOnADORecordset() 
Dim cnn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim str2 As String
Dim rpt1 As Access.Report
Dim bol1 As Boolean
   
'Open the Connection object
Set cnn1 = New ADODB.Connection
cnn1.Open "Provider=Microsoft.Jet.O