ACC: How to Automate Changes to Properties of a Graph Axis

• Microsoft Access, versions 2.0, 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article describes a method for programmatically changing the properties of a graph on a form. The example shows you how to change the Minimum and Maximum Scale, and the Minor and Major Unit properties of the Y-axis on a Microsoft Graph object embedded in a form.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

In the following example, you create a form with a graph, four text boxes, and a command button. You type the values for Minimum Scale, Maximum Scale, Minor Unit, and Major Unit in the text boxes, and then click the button to see the changes in the graph.

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

2. Create a new form following the steps for your version of Microsoft Access:

   In Microsoft Access 7.0 and 97:

       a. Using the Chart Wizard, create a new form based on the Orders table.
   

       b. 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.
   

       c. In the "What type of chart would you like" dialog box, click Column
          Chart, and then click Next.
   

       d. 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.
   

       e. In the "What title would you like for your chart" dialog box, type
          Orders by Employee, click "Modify the design of the form or the
          chart," and then click Finish.
   

   In Microsoft Access 2.0:

       a. Create a new form based on the Orders table using the Form Wizards.
   

       b. In the "Which Wizard do you want?" dialog box, click Graph, and then
          click OK.
   

       c. 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.
   

       d. 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.
   

       e. In the "How do 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.
   

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

       g. In the "What title do you want on your chart?" dialog box, type
          Orders by Employee, and then click Next.
   

       h. In the "Those are all the answers the Wizard needs to create your
          graph" dialog box, click "Modify the design of the form or the
          graph," and then click Finish.
   

3. When the form opens in Design view, set the Name property of the graph object to GraphOrders.

4. Add the following controls to the detail section of the form:

         Text Box:
            Name: MinScale
         Text Box:
            Name: MaxScale
         Text Box:
            Name: MinorUnit
         Text Box:
            Name: MajorUnit
         Command Button:
            Name: ChangeGraph
            Caption: Change Graph
            OnClick: [Event Procedure]
   
   

5. Click the Build button next to the OnClick property of the command button and type the following code:

   NOTE: In the following sample code, 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 code in version 2.0.

         Private Sub ChangeGraph_Click()
         Me![GraphOrders].object.application.chart.axes(2).minimumscale= _
             CDbl(Me![MinScale])
         Me![GraphOrders].object.application.chart.axes(2).maximumscale= _
             CDbl(Me![MaxScale])
         Me![GraphOrders].object.application.chart.axes(2).minorunit= _
             CDbl(Me![MinorUnit])
         Me![GraphOrders].object.application.chart.axes(2).majorunit= _
             CDbl(Me![MajorUnit])
         End Sub
   
   

6. Save the form as frmOLEGraph and switch it to Form view.

7. Note the appearance of the graph. Type 1 in the MinScale box, 100 in the MaxScale box, 5 in the MinorUnit box, and 20.5 in the MajorUnit box.

8. Click the Change Graph button, Note that the Y-axis ranges from 1 to 100 in increments of 20.5.

REFERENCES

For more information about changing graph objects, search the Help Index for "graph" or "graphs," or ask the Microsoft Access 97 Office Assistant.