Access/MS Graph Question...
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.
[1750 byte] By [
merlicky] at [2007-11-11 8:13:54]
# 4 Re: Access/MS Graph Question...
For number 1, I added code to the OLEObjects 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 Charts 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 Charts 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 LegendEntrys Colors for that entry value.
