Charts, how much control do you get programatically

  • Thread starter Thread starter Michelle
  • Start date Start date
M

Michelle

Hi, Using Access 2003

I have a button on a form which will open another form that just contains a
chart. The chart's rowsource is based on a query from the originating form.
All good so far.

Once I have set the initial settings (type of chart, etc) how can I
programatically change things like type of chart, title, etc I know I can
go to Chart, Options but I want to allow my user to click on different
settings and change these things on the fly for them. What is the object,
properties or methods available to me, or if there is a link/s that I can
read up, that is also much appreciated.

TIA for your response.
Michelle

**please only reply to newsgroup**
 
Too bad that no one had an answer to this question yet.
I'm very interested in information concerning this subject as well. Or maybe
an .mdb example. Can't really find anything so far.

- Onne (NL)
 
Onne said:
Too bad that no one had an answer to this question yet.
I'm very interested in information concerning this subject as well.
Or maybe an .mdb example. Can't really find anything so far.
Click to expand...

That's a bit difficult to state without considerable research. You can add a
reference to MS Graph to an MDB and then use the Object Viewer to see all the
methods and properties exposed by the graph engine. You can pretty much do
*anything* to a chart programmatically.

You don't need the reference to be able to do these things, just to be able to
use the Object Viewer to dig the methods up.
 
The query by form applet found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane has the
ability to query your data and display it as a graph on a form. There is
code that allows the user to change the chart type based on the value from a
combo box. Another snippet of code changes the chart title.

When I want to use code to modify a chart in Access, I generally:
-create a similar chart in Excel
-turn on the Macro Recorder
-manually make the change to the chart such as the scale or colors or
whatever
-stop the Macro Recorder
-find the code created by the recorder and copy it into my form or report
-modify the code to reference the appropriate chart object
-test and possibly go back a step and repeat
 
Add the Microsoft Graph Object Library to the References of your database
and the use the ObjectBrowser to browse through the Objects / Properties
available in Graph.

For example, here is a bit of code to manipulate a Chart I wrote few years
ago:

****
Private Sub Form_Load()
'================
' Form_frmGraph_LL.Form_Load
'--------
' Purpose:
'--------
' Notes :
'--------
' Parameters:
'
'--------
' Called Subs/Functions
' (none)
'--------
' Calling Subs/Functions
' (none)
'--------
' Returns:
' (none)
'--------
' Author : Van T. Dinh, Monday, 16 June 2003
'--------
' Revision History
' Monday, 16 June 2003 (VTD):
'================
On Error GoTo Form_Load_Err
Const QUERYNAME As String = "qqptGraph"

Dim objChart As Graph.Chart
Dim dblChart_Min As Double
Dim dblChart_Max As Double
Dim dblChart_Range As Double
Dim dblChart_ExRange As Double

Dim dblMin_RMin As Double
Dim dblMin_RMax As Double
Dim dblMin_SMin As Double
Dim dblMin_SMax As Double
Dim dblMax_RMin As Double
Dim dblMax_RMax As Double
Dim dblMax_SMin As Double
Dim dblMax_SMax As Double

Set objChart = Me.oleGraph.Object
With objChart
' Show Chart Title if available
If Len(Trim(Me.OpenArgs & "")) > 3 Then
.HasTitle = True
.ChartTitle.Text = Nz(Me.OpenArgs, " ")
Else
.HasTitle = False
End If

' Set Chart Min, Max, (extended) Range
If DCount("*", QUERYNAME) > 0 Then
dblMin_RMin = Nz(DMin("[Result Min]", QUERYNAME, "[Result Min] Is Not
Null"), 0)
dblMin_RMax = Nz(DMin("[Result Max]", QUERYNAME, "[Result Max] Is Not
Null"), 0)
dblMin_SMin = Nz(DMin("[Spec Min]", QUERYNAME, "[Spec Min] Is Not
Null"), 0)
dblMin_SMax = Nz(DMin("[Spec Max]", QUERYNAME, "[Spec Max] Is Not
Null"), 0)
dblMax_RMin = Nz(DMax("[Result Min]", QUERYNAME, "[Result Min] Is Not
Null"), 0)
dblMax_RMax = Nz(DMax("[Result Max]", QUERYNAME, "[Result Max] Is Not
Null"), 0)
dblMax_SMin = Nz(DMax("[Spec Min]", QUERYNAME, "[Spec Min] Is Not
Null"), 0)
dblMax_SMax = Nz(DMax("[Spec Max]", QUERYNAME, "[Spec Max] Is Not
Null"), 0)
dblChart_Min = fnZeroIgnoredMin(dblMin_RMin, dblMin_RMax, dblMin_SMin,
dblMin_SMax, _
dblMax_RMin, dblMax_RMax, dblMax_SMin,
dblMax_SMax)
dblChart_Max = fnZeroIgnoredMax(dblMin_RMin, dblMin_RMax, dblMin_SMin,
dblMin_SMax, _
dblMax_RMin, dblMax_RMax, dblMax_SMin,
dblMax_SMax)
dblChart_Range = Abs(dblChart_Max - dblChart_Min)

dblChart_Min = Round(dblChart_Min - 0.1 * dblChart_Range, 1)
dblChart_Max = Round(dblChart_Max + 0.1 * dblChart_Range, 1)
If (Abs(dblChart_Max - dblChart_Min) <= 4# * 10 ^ -2) Then
dblChart_Max = dblChart_Min + 0.1
End If
dblChart_ExRange = dblChart_Max - dblChart_Min

' Set Chart Min, Max, (extended) Range, MinScale, MaxScale & MajorUnit
.Axes(Graph.xlValue).MinimumScale = dblChart_Min
.Axes(Graph.xlValue).MaximumScale = dblChart_Max

If dblChart_ExRange > 4# Then
.Axes(Graph.xlValue).MajorUnit = Round((dblChart_Max - dblChart_Min)
/ 4, 0)
ElseIf dblChart_ExRange > 0.4 Then
.Axes(Graph.xlValue).MajorUnit = Round((dblChart_Max - dblChart_Min)
/ 4, 1)
Else
.Axes(Graph.xlValue).MajorUnit = Round((dblChart_Max - dblChart_Min)
/ 4, 2)
End If

' Set axes crossing
.Axes(Graph.xlValue).Crosses = Graph.XlAxisCrosses.xlAxisCrossesCustom
.Axes(Graph.xlValue).CrossesAt = dblChart_Min
.Axes(Graph.xlCategory).Crosses = 1
End If
End With
Set objChart = Nothing

Form_Load_Exit:
Exit Sub

Form_Load_Err:
Select Case Err.Number
Case 0
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf &
vbCrLf & _
"(Programmer's note: Form_frmGraph_LL.Form_Load)" & vbCrLf, _
vbOKOnly + vbCritical, "Run-time Error!"
End Select
Resume Form_Load_Exit
End Sub
****
 
Back
Top