Microsoft KB Archive/209993

= ACC2000: How to Set the RowSource Property of a Report Graph Dynamically =

Article ID: 209993

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209993



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
This article shows you how to use code to set the RowSource property of a Graph object in a Microsoft Access report.



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 can't set the Row Source property after printing has started.

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
 Open the sample database Northwind.mdb. Create a new report not based on any table or query in Design view. Add a graph to the Detail section of the report showing the total number of customer orders taken by each employee.  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. In the Which table or query would you like to use to create your chart? dialog box, select Orders, and then click Next. 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.</li> In the What type of chart would you like? dialog box, click Column Chart, and then click Next.</li> 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.</li> In the What title would you like for your chart? dialog box, click Finish.</li></ol> </li> Set the Name property of the Graph object to GraphTest.</li> Save the report as OrdersPerEmployee, and then close it.</li></ol>

Creating the Macro
<ol>  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. <pre class="fixed_text">  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 </li> Save the macro and close it.</li></ol>

Adding the Macro to a Button on a Form
<ol> Open the Customers form in Design view.</li> Add a command button to the Form Header section of the form and set the following properties:

Name: OpenReport

Caption: Preview Graph

OnClick: RptGraph

</li> 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.</li> Close the report and return to the Customers form.</li> 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.</li></ol>

<div class="references_section">