Access/MS Graph Question...

[merlicky]

I am encountering a few problems with MS Graph through Access VBA. Any help would be appreciated.

1) The graph variables do not update fast enough to pull out the data to update the graph during creation at runtime (For instance: I would like to reset the CrossesAt to the MinimumScale value so that my Axis labels are in the correct place when the value drops below zero. Also, I would like to be able to read the SeriesCollection.Count value.).

What ends up happening is that the default variables from the stored graph in the form are read instead of the new information from the query creating the graph. Once the graph has been opened the data is read correctly though (tested by adding a command button to the form and spitting out a message box with the information.) I need to be able to do this during creation at runtime.

Is there any way to fix this? I've tried:

Form("").OLEObject.Requery
Form("").OLEObject.Object.Application.Chart.Update
Form("").OLEObject.Object.Application.Chart.Requery

None of these have worked.

2) Is there any way to read Text information from the Legend or Series Collections? I want to change the color of the lines in my bar graph depending on the name of the series (For instance: I would like to change the color of my lines and points to red for the series named "TOTAL".).

I have tried using typical terms like Caption, Text, Value, and Name with each of the items but these are not recognized.
(Ex: Forms("").OLEObject.Object.Application.Chart.SeriesCollection.____
or ...Chart.SeriesCollection(1).____
or ...SerriesCollection(1).Series.____
or ...Chart.Legend.LegenEntries(1).____ etc...)


Thanks.

[merlicky]

Okay. I've solved all my problems. If anyone wants to know, I can post what I figured out.

[cyburt]

Please post your solution for problem #1. That would help me out greatly! I've been trying to solve this problem for years. I have a client who runs reports bi-annually and every once in a while a graph will display the values that were set at design-time. Very frustrating...

[bethg]

If you are still checking this thread, I would love the answer to question 2.
Thanks.

[merlicky]

For number 1, I added code to the OLEObject’s Updated event instead of running it as the form loads. That way it is ran AFTER the graph is updated with the new RowSource query. The Updated code is triggered when the Chart’s RowSource is set through the code that calls the form.

Since I wanted this to run only once and not keep looping with every change, I used the object's Tag to determine whether the code had already been run. Before I updated the Chart’s RowSource I set the Tag to False. In my Updated sub I had an IF statement run if the Tag = False. Inside the IF statement I set the Tag = True.

For number 2, there are a number of different variables that need to be used. I first set these object variables:

Set OLEObj = Me.OLEGraph.Object 'Accesses the OLE object
Set DataObj = OLEObj.Application.DataSheet 'Accesses the Datasheet of the OLE object
Set ChartObj = OLEObj.Application.Chart 'Accesses the Chart of the OLE object

Others references:

OLEObj.SeriesCollection.Count 'Determines the number of items graphed/items in legend
ChartObj.Legend.LegendEntries(<EnteryNumber>).LegendKey.Border.Color = "255" 'Sets the line color/border color for item
ChartObj.Legend.LegendEntries(<EnteryNumber>).LegendKey.MarkerBackgroundColor = "190" 'Sets color for Marker background
ChartObj.Legend.LegendEntries(<EnteryNumber>).LegendKey.MarkerForegroundColor = "190" 'Sets color for Marker foreground
DataObj.Cells(<Row>, <Column>) 'Like with Excel, this reads/writes to this position in the datasheet

With these variables I could loop through data in the data sheet and find the information that I wanted to change. Then knowing the Cell location of the data, I could manipulate the LegendEntry’s Colors for that entry value.