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 shows you how to use code to change 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.

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

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 by following the steps for your version of Microsoft Access:
   
   In Microsoft Access 7.0 and 97:
   
   
   1. Using the Chart Wizard, create a new form based on the Orders table.
   
   
   
   2. 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.
   
   
   
   3. In the "What type of chart would you like" dialog box, click Column Chart, and then click Next.
   
   
   
   4. 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.
   
   
   
   5. 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:



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



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



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



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, type Orders by Employee, and then click Next.



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

Additional query words: format

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