Select the product you need help with

HOW TO: Use Automation to Set Microsoft Graph Trend Lines in Access 2000

Article ID: 210024 - View products that this article applies to.

This article was previously published under Q210024

SUMMARY

This article shows you how to use Automation to add, remove, and set trend lines on a Microsoft Graph object.

There are different types of trend measurements. 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 apply only to two-dimensional graphs of the following types: Bar, Column, Line, and Scatter. The following examples use a bar graph.

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.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Add a Trendline and Set Its Type

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

Start Microsoft Access and open the sample database Northwind.mdb.
Create a new form not based on any table or query in Design view.
On the Insert menu, click Chart, and then click the Detail section of the form to start the Chart Wizard.
In the Which table or query would you like to use to create your chart? dialog box, click Queries, and then click Employee Sales By Country. Click Next.
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 appears in the form.
On the View menu, click Properties. Select Graph so that you are viewing the Graph object's properties. Set the Name property to MyGraph.
Add a command button with the following properties to the form:
Command button:
Caption: Add Trend Line
OnClick: =AddTrendLine()
On the View menu, click Code to view the form's module.

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

Switch the form to Form view. When prompted for a beginning and ending date, type 1/1/97 and 1/1/98 respectively. Click the Add Trend Line button. Note that the trend line is added to the chart.

Remove a Data Series Trendline

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

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()
On the View menu, click Code to view the form's module.

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

Switch the form to Form view. When prompted for a beginning and ending date, type 1/1/97 and 1/1/98 respectively.
Click the Add Trend Line button to add the trend line to the chart.
Click the Remove Trend Line button. Note that the trend line is removed from the chart.

Back to the top | Give Feedback

REFERENCES

For more information about charts, click Microsoft Access Help on the Help menu, type create a chart in a form or report in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about various types of trendlines, click Microsoft Access Help on the Help menu, type about choosing the best trendline for your data in a chart in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Back to the top | Give Feedback