ACC: Set the RowSource Property of a Report Graph Dynamically

ID: Q158927


The information in this article applies to:
  • Microsoft Access versions 2.0, 7.0, 97


SUMMARY

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

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

NOTE: A demonstration of the technique used in this article can be seen in the sample file, Grphsm97.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

Q186855 ACC97: Microsoft Access 97 Sample Graphs Available in Download Center


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.

Microsoft Access 7.0 and 97

You can't set the Row Source property after printing has started.

Microsoft Access 2.0

Can't set property 'Row Source' 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: Following the steps in this example will modify the sample database Northwind.mdb (or NWIND.MDB in Microsoft Access 2.0). You may want to back up the Northwind.mdb (or NWIND.MDB) file and perform these steps on a copy of the database.

Creating the Graph Report

  1. Open the sample database Northwind.mdb (or NWIND.MDB in 2.0).


  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.

    In Microsoft Access 7.0 and 97:

    1. On the Insert menu, click Chart, and then click in the detail section of the report to invoke the Chart Wizard.


    2. In the "Which table or query would you like to use to create your chart?" dialog box, select the Orders table, 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 blank, and then click Next.


    6. In the "What title would you like for your chart" dialog box, click Finish.


    In Microsoft Access 2.0:

    1. Click the Graph button on the Toolbox toolbar, and then click in the detail section of the report to invoke the Graph Wizard.


    2. In the "Where do you want your graph to get its data?" dialog box, select the Orders table and then click Next.


    3. In the "Which fields contain the data you want for your graph?" dialog box, add Order ID and Employee ID to the Fields For graph box, and then click Next.


    4. In the "What categories do you want along the graph's axis?" dialog box, add Employee ID to the Categories For Axis box, and then click Next.


    5. In the "How to you want to calculate the totals for each category on your graph?" dialog box, click "Count the number of records in each category," and then click Next.


    6. In the "What type of graph do you want?" dialog box, click Next.


    7. In the "What title do you want on your chart?" dialog box, click Finish.




  4. Set the Name property of the graph object to GraphTest.


  5. Save the report as OrdersPerEmployee and 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.

    NOTE: In the SetValue expression of the following macro, type a space in [Employee ID], [Order ID], and [Customer ID] in Microsoft Access 2.0.
    
        Macro Name   Action
        -----------------------
        RptGraph     Echo
                     OpenReport
                     SetValue
                     RunCommand (or DoMenuItem in versions 2.0 and 7.0)
                     OpenReport
                     Echo
    
        RptGraph Actions
        ------------------------------------------------------------
        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];" 
    In Microsoft Access 97 only, use the following Runcommand:
    
        RunCommand
           Command: Save 
    In Microsoft Access 2.0 and 7.0 only, use the following DoMenuItem:
    
        DoMenuItem
           Menu Bar: Report
           Menu Name: File
           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:
    
          Command Button
          --------------
          Name: OpenReport
          Caption: Preview Graph
          OnClick: RptGraph 


  3. Open the form in Form view and 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.



REFERENCES

For more information about the RowSource property, search the Help Index for "RowSource property," or ask the Microsoft Access 97 Office Assistant.

Additional query words:

Keywords : kbinterop kbprg IntpGrph
Version : WINDOWS:2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto


Last Reviewed: November 13, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.