Modifying Charts in Reports

[Wicklund]

What I have is more of an annoyance than a problem.

I have successfully added/edited a chart to my report. The annoying part, is that when I go to format the chart (Using Microsoft Graph), it doesn't reflect the data that my chart is based off. In order to preview my changes, I need to preview the report. Then, in order to make an additional change, I need to go into the Design View of the report, and then Microsoft Graph again to make the changes.

Is there a way to link it, so that when making format changes in Microsoft Graph, is is showing the actual graph?

Thank you,

 

[ssmithri]

I am not sure if you are still interested, but, I believe I have found your answer:

http://ourworld.compuserve.com/homepages/attac-cg/ARptTip.htm#GRAPHGEN

Some of the most common questions related to Access reports involve problems with Microsoft Graph or formatting Graph presentations. Here's a few common issues and how to solve them with Visual Basic.

1) Problem: When the report is previewed or printed, Graph doesn't display the proper data from its record source, instead it displays either the data from a prior record or the sample data in the Graph data sheet:

Solution:

Add the following Visual Basic code to the On Print event of the section of the report that contains the Graph object.


On Error Resume Next
Dim objGraph As Object
Set objGraph = Me!TheNameOfYourGraph.Object
objGraph.Refresh
DoEvents
Set objGraph = Nothing
2) The data in the datasheet and sample data shown in Graph in design view is not the data from the record source specified, rather it is some default sample data; so its hard to properly design the graph.
Solution:

Modify the code above adding the following 5th line shown below, preview the report and save it.


On Error Resume Next
Dim objGraph As Object
Set objGraph = Me!TheNameOfYourGraph.Object
objGraph.Refresh
'This will update the data sheet
objGraph.Application.Update
DoEvents
Set objGraph = Nothing

3) Problem: The data table displayed on your Graph will not display the number format you've specified in the Graph's record source (e.g. display only one decimal place by using Format([YourField], "#.0") in the query:

Solution:


Graph sets this formatting in the data sheet view when graph is in design mode; right click on the column that represents the series of data displayed (e.g. column A is series 1, B Series 2 etc.) and choose the "Number format" option.

If Graph still doesn't hold the formatting you desire after setting the format in the datasheet, add code like the following to the On Print event of the same section of the report that contains your Graph:

On Error Resume Next
Dim objGraph As Object
Dim objDS as Object
Set objGraph = Me!TheNameOfYourGraph.Object
Set objDS = objGraph.Application.DataSheet
'Singe decimal place, 200 data points
'Format is the same as Excel VBA
objDS.Range("A1:A200").NumberFormat = "#.0%"
objGraph.Refresh
DoEvents
Set objGraph = Nothing