Dynamically Updating a Report

You can use VBA to add a new record source property to a report. While your application causes a report's content to change, you can update the report's title to reflect the new content. If you present the results of a parameter-like query in a report, you can also use many formatting options for the result.

Figure 6-16 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 comparison, the user can click the command button to open the report on the right in Design view. This view is necessary to add a new record source property and to reset the Caption property for the label that displays the report's 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. The instructions below the title explain how to close the form.

Figure 6-16. 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 below) for the command button performs three tasks. First, it constructs a SQL string based on selections made in the form. The procedure successively adds clauses to a starter statement that lists product name and unit price from the Products table. It 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 (>) 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 and caption for a label on a report. After opening the report in Design view, it 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 the next 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 in the first part of the procedure. Then it 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.

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 strSQL As String, strOperator As String Dim strWhere As String 'Set up SQL statement for report record source.     strSQL = "Select ProductName, UnitPrice " & _         "from Products"     strOperator = Choose(optRule, ">", ">=", "=", "<=", "<")     strWhere = "Where UnitPrice" & strOperator & txtAmount     strSQL = strSQL & " " & strWhere & " Order By UnitPrice"     If optRule <= 2 Then         strSQL = strSQL & " 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 strSQL      '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")         .Visible = False         .RecordSource = strSQL         .Controls("lblTitle").Caption = _         "Products with a Unit Price " & strOperator & " $" & txtAmount     End With 'Now show the form to the user.     DoCmd.OpenReport "rptProductsfromForm", acViewPreview     DoCmd.Echo True End Sub