MSGraph Chart Problem (1 Viewer)

Hello,

I have set up a report with an OLE-object which is a MSGraph.Chart.8 . The chart data comes from a query to a table which data (statistics) is written new every time a user starts the report (in fact I run code for compiling the statistic data first and then per VBA open the report).

(Short what the statistic is about. The first value is the total amount of records which have been created in a certain time period, the second value is the amount of records which match certain conditions in that same period. And there is a percentage line which gives the ratio between this two in %).

What happens now is, when I open the report, the chart shows occasionally the data from the example table of the graph instead of the real data from the query.

Second thing is, that the chart has to vertical axis, one for the number of records (see above) and one for the percentage value. I would like to force the graph to have both axis start at 0 and cross at 0 with the horizontal axis.

What I did was in Format menu - Axis of MSGraph I dechecked the check boxes for Minimum and set it to 0, as well as I set the cross horizontal axis value to 0. What else do I need for it to work?

I would appreciate any help.

Thank you.
Mary H.

P.S. I've attached a graph as bmp

I am not sure if this applies, but I came across this website, that offered several solutions:

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

Thanks

Hello ssmithri,

thank you for letting me know. The solutions are quite what I've been (still!!!) looking for.

Regards,
Mary.h