need vba to scale Pivot chart

[jscriptor09]

Hi folks



Right now, I have a query set up to write to a table, and a Pivot chart that plots off the table. The data range in the table changes depending on dept/functions and so forth.

I need the ability to rescale the pivot chart Y axis on the fly using vba. The line of thought is that I can determine the range from looking at the table before calling the Form that has the PIVOT chart.

Question:
Given that I have a pivot chart Form in place, how do I point to it using VBA and what would be the logic to change the scale. Please note that I have several forms with PIVOT chart. I need to be able to select specific one out of the collection

Many thanks for you help

[June7]

I don't have any pivot charts and don't have any charts on forms. I do have charts on reports and set axis scales with VBA. Example:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Plot Graphs procedure
Dim MinUWT As Double, MaxUWT As Double
Dim MinDry As Double, MaxDry As Double
Dim MinSoak As Double, MaxSoak As Double
Dim MinRet As Double, MaxRet As Double
With Me
If Not IsNull(!MinOfA) Then
    MinUWT = Int(!MinOfU) - Int((10 - (Int(!MaxOfU) + 1 - Int(!MinOfU))) / 2)
    MaxUWT = MinUWT + 10
    MinDry = Int(!MinOfD / 5) * 5 - (50 - Int((Int(!MaxOfD / 5) * 5 + 5 - Int(!MinOfD / 5) * 5) / 10) * 10) / 2
    MaxDry = MinDry + 50
    MinSoak = Int(!MinOfS / 5) * 5 - (50 - Int((Int(!MaxOfS / 5) * 5 + 5 - Int(!MinOfS / 5) * 5) / 10) * 10) / 2
    MaxSoak = MinSoak + 50
    MinRet = Int(!MinOfR / 5) * 5 - (50 - Int((Int(!MaxOfR / 5) * 5 + 5 - Int(!MinOfR / 5) * 5) / 10) * 10) / 2
    MaxRet = MinRet + 50
    .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 = MinRet
    .gphITSret.Axes(xlValue).MaximumScale = MaxRet
    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

[jscriptor09]

Thank you June7

The problem is that I don't know what is the equivalent of Me.gphWeight (for example) in a form that has a PivotChart in it.

I am hoping someone will have a clue of the object model behind the pivotchart.

Thanks

[June7]

Oh, think I see issue. I have never set a form to the PivotChart View. These seem to have clues.
http://www.msaccessanswers.com/Forms...t-with-VBA.jsp
http://office.microsoft.com/en-us/ac...001034579.aspx
http://support.microsoft.com/kb/304143
http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

[jscriptor09]

Hi June7

Many thanks for the references. One of them hit the mark.

I would like to set the chart majortick value on the Primary Y as follows:
1- Get whatever Primary Y value ACCESS decides to give the chart based on the data
2- Adjust upward so the number is divisible by 10 (or some other logic).
3- set majorTicks to the value determined in step 2.

Here is what I have:

Private oChart As ChChart
Private oCategoryAxis As ChAxis
Private oValueAxis As ChAxis
Private oBackWall As ChSurface

Private Sub button_Click()
Dim maxY As Integer
'
'Activate the Chart
If Not CurrentProject.AllForms("frm_VDR_OTD_Chart").IsLoa ded Then
DoCmd.OpenForm "frm_aChart", acFormPivotChart, , , , acHidden
End If

' theMax = Call getMax <<<<<<<<<< I am stuck here

Set oChart = Forms("frm_aChart").ChartSpace.Charts(0)
Set oCategoryAxis = oChart.Axes(0)
Set oValueAxis = oChart.Axes(1)
'oValueAxis.MajorUnit = Abs(theMax / 10) ' sort of what I am trying to do
DoCmd.Close acForm, "frm_aChart", acSaveYes
End Sub

The reason I need to control the majortick is because I have a secondary axis % going from 0 to 100% which has own majorTicks. I want to make sure that the Primary and secondary axis majortick lines line-up.

Is there a way to retrieve the max Y value that the chart object is going to use for the chart (I let the chart auto scale)

Thanks for the help

[June7]

Of course, how depends on your data.

All of my graphs are based on UNION queries because of the table structures. Essentially I have to 'normalize' the data for graphing.

I use a subquery in the Report RecordSource that returns the Max and Min values from the UNION query. This makes the values available for the code to calculate scale range.

[ddk]

Does the solution above work in a compiled version of the database (.mde, .accde)?

[June7]

I have never used mde or accde. However, I have doubts. Try it and let us know.