ACC2000: How to Set the RowSource Property of a Report Graph Dynamically (209993)

MORE INFORMATION

If you try to set the RowSource property of a Graph object on a report in Print Preview, you may receive the following error message. You must open the report in Design view first, set the RowSource property of the Graph object, and then print or view the report. In the following example, you create a macro to open a report in Design view, set the RowSource property of a graph, and then display the report in Print Preview. Then the macro is attached to a command button on a form to dynamically change the contents of the graph based on the current record on the form.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Creating the Graph Report

1. Open the sample database Northwind.mdb.
2. Create a new report not based on any table or query in Design view.
3. Add a graph to the Detail section of the report showing the total number of customer orders taken by each employee.
   1. On the Insert menu, click Chart, and then click the upper-left corner of the Detail section of the report to start the chart wizard.
   2. In the Which table or query would you like to use to create your chart? dialog box, select Orders, and then click Next.
   3. In the Which fields contain the data you want for the chart? dialog box, add OrderID and EmployeeID to the Fields for Chart box, and then click Next.
   4. In the What type of chart would you like? dialog box, click Column Chart, and then click Next.
   5. In the How do you want to lay out the data in your chart? dialog box, drag OrderID to Data (at the upper-left of the graph). Drag EmployeeID to Axis (at the bottom of the graph). Leave Series (on the right) blank, and then click Next.
   6. In the What title would you like for your chart? dialog box, click Finish.
4. Set the Name property of the Graph object to GraphTest.
5. Save the report as OrdersPerEmployee, and then close it.

Creating the Macro

1. Create a new macro called RptGraph with the following actions. NOTE: In the SetValue expression of the following macro, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this expression.

      Action      Action Arguments
      -------------------------------------------------------------------
      Echo        Echo On: No
      OpenReport  Report Name: OrdersPerEmployee
                   View: Design
      SetValue    Item: Reports!OrdersPerEmployee!GraphTest.RowSource
                  Expression: "SELECT [EmployeeID], Count([OrderID]) as _
                  [Number of Orders] FROM [Orders] where [CustomerID] = _
                  Forms!Customers![CustomerID] GROUP BY [EmployeeID];"
      RunCommand  Command: Save
      OpenReport  Report Name: OrdersPerEmployee
                  View: Print Preview
      Echo        Echo On: Yes
   					

2. Save the macro and close it.

Adding the Macro to a Button on a Form

1. Open the Customers form in Design view.
2. Add a command button to the Form Header section of the form and set the following properties:

   Name: OpenReport
   Caption: Preview Graph
   OnClick: RptGraph

3. Open the form in Form view, and then click the Preview Graph button. Note that the graph report opens and shows a count of orders taken, by employee, for the current customer record.
4. Close the report and return to the Customers form.
5. Move to a different customer record and click the Preview Graph button. Note that the graph report opens and shows orders, by employee, for the current customer record.