MS Access - Graphing, Adjust scale via code

[ironfelix717]

Hi, not sure if this belongs in VBA section or not, please move if wrong section.


I'm not sure whats worse -- Excel's graphing tools or Access.

I am using a "non modern" line chart (because the modern charts are nearly worthless).


I was pleased to know a fair bit of formatting can be done on the old school charts. However, i was wondering if any of this can be done via code?

if I want to adjust the scaling of the secondary Y axis in my line chart, how would I do that in code? (set the max, min values of the axis)
There's almost a hundred properties in Intellisense, where probably only half are listed in Access' properties pane.

[June7]

Review https://myengineeringworld.net/2015/...-axis-vba.html

I have done this with primary Y-axis, not secondary. Also, all charts I have done this with are XYScatter type. Example:

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

xlValue is Y-axis, xlCategory is X-axis

[ironfelix717]

With Me
.gphWeight.Axes(xlValue).MinimumScale = MinUWT

June7,

The review is excel so there's nothing really of value there.

I assume your example is for an Access chart. If so, there is no

.Axes

property of the object i am using (combo chart - which is really just a linegraph with more than 1 data series).

By "no property" i mean, intellisense doesn't recognize it as a property, which I know is sometimes the case with old activeX controls.

Not sure if this is possible or not.

[ironfelix717]

UPDATE:

Did a bunch of reading specifically for keywords "MSGraph.Chart.8 Class properties".

Intellisense still doesn't work, even when having the MS Graphs 16.0 Object Library reference.
So, play a nice game of find the property with pure luck. My favorite.

I was able to change the plot area using...

Dim obj     As Object
Set obj = graphAFR.Object.Application.Chart


obj.PlotArea.Height = 2 * 1440   'WORKS
obj.Axes(xlSeries, xlPrimary).MinimumScale = 7  'DOESNT WORK - CANT FIND 'AXES PROPERTY'

Here is the object model - i think? Does anyone even know? - Does MS even know?
https://docs.microsoft.com/en-us/pre...ectedfrom=MSDN



Noteworthy resources:
https://vb123.com/create-dynamic-chart-applications/ - no valuable code unfortunately.
http://www.utteraccess.com/forum/cha...-t2003242.html



Sad - give me a professional tool MS!

[June7]

I missed that detail. However, the code is a clue to what Access can do.
I did a test. Built a combo line/bar graph on form. Button click event code:

Private Sub Command1_Click()
Debug.Print Me.Graph0.Axes(xlValue, xlSecondary).MinimumScale
End Sub

The correct value is displayed.

I do get intellisense for .Axes.

If you want to provide db, follow instructions at bottom of my post.

[ironfelix717]

June,

Thanks for the reply.

I don't think you're using the same type of object as me.
Though its a "graph", it uses a different OLEClass perhaps. A genius working of MS to confuse everyone.
I could be wrong.

What I do know: there is absolutely no .Axes property in this chart object and you can see for yourself here:


TestGraph.zip


Regards

[June7]

I am using old MSGraph in Access 2010. I do not get the intellisense with your file and now not with mine. I did get it with the new form and line/bar graph in my test. But dang, not replicating that today. I let Windows run updates after I did that test. Wonder if that has something to do with issue. Agree, really sucks.

Code compiles in the db posted procedure came from, report opens but graphs have no data. This file has worked for over 10 years and blown up overnight. This really, really sucks.

Also, can no longer connect to https://www.access-programmers.co.uk

Removing the updates hasn't helped.

[ironfelix717]

June7,

Sorry to hear about the inconveniences on your end. Godspeed in fixing those.
Windows updates are the bane of computing.

I'll resort to Access Programmers and see if anyone over there can figure out whats going on.

Thanks for the help!

[June7]

Okay, can only wish you luck.

My graphs are still not working. So bummed!

Access Programmers site is now back so issue must have been their end.

[isladogs]

June
I've been unable to connect to AWF since yesterday afternoon.
Still down for me (invalid security certificate) though I'm getting occasional email notifications.

[June7]

Yes, that was what I was getting as well. No idea why AWF OK now.

[isladogs]

Deleted comment. Off topic

[ironfelix717]

Update: Moved conversation to: https://www.access-programmers.co.uk...a-form.310243/

UNSOLVED