Difficulty Adjusting Scales on an Access Form Chart in VBA

[kvogt]

Access wizards,

I am currently having a problem when trying to automatically set the minimum and maximum scale values on the axis of a chart on an Access Form. Here is the VBA code I have thus far:

Forms![SawToothPilot]![GraphByItemNum].Object.Application.Chart.Axes(xlSecondary).MinimumScale = 3

I know you can set the scale values manually, but eventually, I need to have similar code so I can dynamically set scale values based on query results.

Anytime this code executes I get the following error:

Run Time Error '1004': Unable to set the MinimumScale property of the Axis class.

Please advise.
Thanks,
Kevin.

 

[dRahme]

Hi,

Run Time Error '1004': Unable to set the MinimumScale property of the Axis class.

check the chart type you are using. Some chart types will not support the statement. Try scatter or pie, for instance.

 

[TheAceMan1]

How are ya kvogt . . . . .

Access uses MicroGraph for charting, and fortunately there's a [blue]Reference Library[/blue] you can use to [blue]make things easier for yourself.[/blue]

In any VBE code window, select Tools - References. Your looking for [purple]Microsoft Graph 9.0 Object Library[/purple]. Check the box and click OK.

Now, in the VBE code window for the form, add the following public subroutine (you substitute names in [purple]purple[/purple]):

[blue]Public Sub SetScale()
   Dim MyChart As Object
   
   Set MyChart = Me![purple][b]YourChartControlName[/b][/purple]
   
   With MyChart.Axes(xlValue)
       .MinimumScale = 20
       .MaximumScale = 90
   End With
   
   Set MyChart = Nothing

End Sub[/blue]

You can call the routine as it suites your needs.

Bear in mind if you set either scale value, [blue]MaximumScaleIsAuto[/blue] property is set to False.

I'm not a charting GuRu, but I have used the library several times in the past. There's a [blue]great abundance[/blue] of things you can do with a chart here!

[blue]Cheers![/blue]

See Ya! . . . . . .

 

[kvogt]

Okay, thanks for the tips, but I am still getting the same errors.

I am using a simple line graph w/ two axes, a primary (left side) and secondary (right side). I only want to dynamically calculate the scale values for the secondary axis.

I added reference to the MicroGraph 9.0 Library along w/ the following subroutine:

__________________________________
Public Sub SetScale()
Dim MyChart As Object

Set MyChart = Me!GraphByItemNum

With MyChart.Axes(xlValue, xlPrimary)
.MinimumScale = 20
.MaximumScale = 90
End With

Set MyChart = Nothing

End Sub
___________________________________

Unforetunately, I still get the same error:
Run Time Error '1004': Unable to set the MinimumScale property of the Axis class.

This could be due to the graph type like dRahme mentioned, but I am still unsure if this is possible or not. Any further thoughts or advice would be greatly appreciated. I am struggling to get through this part of the project that I thought would be so simple.

 

[TheAceMan1]

[blue]I am using a simple line graph w/ two axes, a primary (left side) and secondary (right side). I only want to dynamically [purple]calculate the scale values for the secondary axis[/purple].[/blue]

Change [blue]With MyChart.Axes(xlValue, xlPrimary)[/blue]
To [blue]With MyChart.Axes(xlValue, [purple]xlSecondary[/purple])[/blue]

See Ya! . . . . . .