Graph/Chart in a report

[f15e]

I have a graph in my form (it's not in a subform, just place directly in my form) and use the following to alter it:



Dim graph_obj As Object

Set graph_obj = Me.NameOfMyGraph.Object

NameOfMyGraph.Axes(2).MaximumScale = 400

This works in a Form, but I have a Report (directly in the report) which has a graph and doesn't work when using the same code as above. I get the error "The bound or unbound object frame you tried to edit does not contain an OLE object." I've tried several other ways to get it to work with no luck. Is the syntax different when a chart is in a report? If so, what is it? Thank you.

[June7]

What event is the code in? AFAIK, must be Format event of the section graph is in. Which means only works in PrintPreview or direct to printer.

Example from my db:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'format graphs
Dim MinUWT As Double, MaxUWT As Double
Dim MinDry As Double, MaxDry As Double
Dim MinSoak As Double, MaxSoak As Double
Dim MinRat As Double, MaxRat As Double
With Me
If Not IsNull(!MinOfA) Then
    MinUWT = Int(!MinOfU) - IIf(!MaxOfU - !MinOfU <= 2, 3, 1)
    MaxUWT = MinUWT + 8
    MinDry = Int(!MinOfD / 5) * 5 - (40 - Int((Int(!MaxOfD / 5) * 5 + 5 - Int(!MinOfD / 5) * 5) / 10) * 10) / 2
    MaxDry = MinDry + 40
    MinSoak = Int(!MinOfS / 5) * 5 - (40 - Int((Int(!MaxOfS / 5) * 5 + 5 - Int(!MinOfS / 5) * 5) / 10) * 10) / 2
    MaxSoak = MinSoak + 40
    MinRat = Int(!MinOfR / 5) * 5 - (40 - Int((Int(!MaxOfR / 5) * 5 + 5 - Int(!MinOfR / 5) * 5) / 10) * 10) / 2
    MaxRat = MinRat + 40
    .gphWeight.Axes(xlValue).MinimumScale = MinUWT
    .gphWeight.Axes(xlValue).MaximumScale = MaxUWT
    .gphITSdry.Axes(xlValue).MinimumScale = MinDry
    .gphITSdry.Axes(xlValue).MaximumScale = MaxDry
    .gphITSsoak.Axes(xlValue).MinimumScale = MinSoak
    .gphITSsoak.Axes(xlValue).MaximumScale = MaxSoak
    .gphITSret.Axes(xlValue).MinimumScale = MinRat
    .gphITSret.Axes(xlValue).MaximumScale = MaxRat
    If Me!Metric = True Then
        .gphWeight.Axes(xlValue, xlPrimary).AxisTitle.Text = "Unit Weight, kg/cu.cm"
        .gphGradation.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Sieve Size (mm)"
        .gphITSdry.Axes(xlValue, xlPrimary).AxisTitle.Text = "ITS Dry, kg/cu.cm"
        .gphITSsoak.Axes(xlValue, xlPrimary).AxisTitle.Text = "ITS Soaked, kg/cu.cm"
    End If
End If
End With
End Sub

[f15e]

Ok, let's say I put a report on my main form and that report includes a graph or chart, how would I reference the graph to be able to make changes to the upper and lower limits of the Y axis? Is that possible?

[f15e]

Ok so I got it to work in the report but I had to add a button to set focus to before setting the chart object. Originally the report didn't have any buttons but b/c I couldn't get it to work I have to add one to set focus to. Is there any other way to do this without adding a control to set focus to?

[June7]

If the code is behind the form or report that holds the graph, should be no need to declare and set graph object - that's the code example I provided.

Otherwise, here is another example:

Sub FormatVibGraph(strObject As String, strLabNum As String, booMetric As Boolean)
'format Vibratory graph form and report
Dim obj As Object
Dim gc As Object
Dim MinDD As Double
Dim MaxDD As Double
MinDD = Nz(DMin("Den", "GraphVibratory"), 0)
MaxDD = Nz(DMax("Den", "GraphVibratory"), 0)
If strObject Like "Lab*" Then
    Set obj = Reports(strObject)
Else
    Forms(strObject).Controls("ctrVibratory").Form
End If
Set gc = obj("gphDensity")
gc.Activate
If MinDD > 0 Then
    With gc
    .Axes(xlValue).MinimumScale = MinDD
    If booMetric = True Then
        MaxDD = Int(MaxDD / 100) * 100 + 100
        MinDD = MaxDD - 1000
        .Axes(xlValue).MaximumScale = MinDD
        .Axes(xlValue).MinimumScale = MinDD
        .Axes(xlValue).MajorUnit = 200
        .Axes(xlValue).MinorUnit = 40
    Else
        MaxDD = Int(MaxDD / 5) * 5 + 5
        MinDD = MaxDD - 50
        .Axes(xlValue).MaximumScale = MaxDD
        .Axes(xlValue).MinimumScale = MinDD
        .Axes(xlValue).MajorUnit = 10
        .Axes(xlValue).MinorUnit = 2
    End If
    .Axes(xlValue, xlPrimary).HasTitle = True
    If booMetric = True Then
        .Axes(xlValue, xlPrimary).AxisTitle.Text = "Max. Dry Density, Kg/cu.m"
    End If
    .Axes(xlCategory, xlPrimary).HasTitle = True
    If booMetric = True Then
        .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Percent Passing 4.75 mm Sieve"
    End If
    End With
End If
End Sub

[f15e]

Thank you very much for your input June7.
Maybe I missed something along the line but is there a setting where when a user adds new data to the table and the y-axis automatically adjusts to the upper and lower limits if the new data happens to be outside of the previous y-axis limits? Or is that something that needs to be manually or programmatically changed?

[June7]

A chart can be set up to allow automatic adjustment of both scales. My posted code programmatically adjusts Y scale (xlValue) to handle the data. I have another procedure that also adjusts X axis (xlCategory).