ACC: How to Use Automation to Set Graph Trend Lines

MORE INFORMATION

Different types of trend measurements exist. You can use the Type property to select a specific trend to use in your graph. You can also use the Add method to add trend lines to your graph, or use the Delete method to remove trend lines. Trend lines only apply to two-dimensional graphs of type "Bar," "Column," "Line," and "Scatter." The following examples use a bar graph.

Example - Add a Trendline and set its Type

The following example adds a trend line to the first series of data and sets it to be of type Polynomial:

1. Open the sample database Northwind.mdb.
2. Create a new form not based on any table or query in Design view.
3. On the Insert menu, click Chart, and then click in the detail section of the form to start the Chart Wizard.
4. In the "Which table or query would you like to use to create your chart?" dialog box, click Queries, and then select the Employee Sales By Country query. Click Next.
5. In the "Which fields contain the data you want for the chart?" dialog box, add the Country and SaleAmount fields to the Fields For Chart box. Click Finish. Your graph will appear in the form.
6. On the View menu, click Properties. Select the Graph so that you are viewing the graph object's properties. Set the Name property to MyGraph.
7. Add a command button to the form with the following properties:

   Command button:
   Caption: Add Trend Line
   OnClick: =AddTrendLine()

8. On the View menu, click Code to view the form's module.
9. Add the following code to the form's module:

          Function AddTrendLine()
            Dim GraphObj As Object
            Set GraphObj = Me![MyGraph].Object.Application.Chart
            GraphObj.SeriesCollection(1).TrendLines.Add
            GraphObj.SeriesCollection(1).TrendLines(1).Type = 3
          End Function
   					

10. Switch the form to Form view. When prompted for a beginning and ending date, enter 1/1/94 and 1/1/95 respectively. Click the Add Trend Line button. Note the trend line added to the chart.

Example - Remove a Data Series Trendline

The following example removes the trend line from the first series of data being graphed in the earlier example:

1. Use the form created in the first example, and add another command button with the following properties:

   Command button:
   Caption: Remove Trend Line
   OnClick: =RemoveTrendLine()

2. On the View menu, click Code to view the form's module.
3. Add the following code to the form's module:

         Function RemoveTrendLine()
            Dim GraphObj As Object
            Set GraphObj = Me![MyGraph].Object.Application.Chart
            GraphObj.SeriesCollection(1).TrendLines(1).Delete
         End Function
   					

4. Switch the form to Form view. When prompted for a beginning and ending date, enter 1/1/94 and 1/1/95 respectively.
5. Click the Add Trend Line button to add the trend line to the chart.
6. Click the Remove Trend Line button. Note the trend line is removed from the chart.