Solved Help adjusting chart's min/max Y-Axis value using code

[isladogs]

Should I understand that graphs on reports will be no longer working anymore on new versions of Access?

Modern charts do work on reports and since March 2025 (Current Channel) now have full VBA support . . . in A365 ONLY

Have a look at my article and example app at:

Programming Modern Chart Properties

This article demonstrates how to modify the properties of the various modern chart types using code. VBA support was added to the Beta Channel in version 2502 build 18514.20000 released on 17 Jan 2025. It will become available in other channels in the near future.

www.isladogs.co.uk

The modern charts code included will work in reports as well as forms but not all of it will work in A2021

However I've just noticed that you have uploaded an MDB file and the two charts are 'classic' not modern which is why you got the error.
Convert to ACCDB and use a modern chart instead

 

[misterr]

Thank you for answer and examples!

Unfortunately, we are still using Access 2010/2013/2016 on some machines, so we can not switch to modern charts yet.
Also, I understand that MDB databases cannot be converted to ACCDB databases because ACCDB format does not support user-level security, which is critical in our environment.

So, to be sure that I understand:
- VBA for "classic" charts, which is supported in Access 2010/2013/2016 (and also in 2019/2021, but only for forms, not for reports), is not supported in Access 2019/2021 reports? It's a bug of Access 2019/2021? Or I should add some references in order to make it working (or to change priority of references)?
- I've just noticed that the chart report created in Access 2021 is not working in Access 2016 (chart form is working, and in 2021 too, as you can see in test.mdb). Even if I try to create a chart in Access 2016, it's not working, despite the fact that the chart created before (some years ago), using same VBA code, it's still working. Something changed in Access after an Microsoft update? It seems that now I'm unable to create a functional report in Access 2016 or in Access 2021.

 

[DHookom]

I moved the code to the On Format event of the Detail section and it worked as expected.

 

[misterr]

Sorry, I tested now as you suggested both on Access 2016 and Access 2021 and it does nothing. No error on report this time, but also no modification on Min & Max.

 

[June7]

Instead of hijacking a 4-year-old thread, should start your own. New threads get more attention. Reference the old one if you think it has relevant info.

Graph should still work on report. I have an older accdb doing this with classic graphs and still works under Access2021.

You moved code as suggested by Duane?

I have never seen Activate event used.

 

[isladogs]

Agree with @DHookum that the code should be in the Report Detail_Format event but also that the code doesn't do anything in that event either
I believe there are limitations with report code but am not aware of any changes in 2019/2021

When I said my not all my example charts would work in 2021, I was referring to the newer chart types recently added and to the formatting code

To my amazement, I was able to create modern charts in your MDB file - both in a form & a report.
I've used a line chart which should display for correctly in A2021
I've not bothered using code as I doubt that would work in that version

As for ULS, why not convert to .ACCDB and create your own security settings?

 

[DHookom]

Also, code does not run in the report view. I tested using Print Preview which will run the event code. The vba sets the max to 50 which is less than the minimum value.

 

[misterr]

Yes, you're right, code runs ok in Print Preview.
My bad, I tested only in Report View.
All is ok now.
Thank you very much!

 

[DHookom]

This is a common mistake.

 

[isladogs]

Oops. I am well aware of this but obviously must have tested in the default report view. My bad!

Here is the moderm chart report in the MDB file with an unexpected issue in the Chart Title!

 

[misterr]

Today I tested here at work on all versions of Access. Now the things are the opposite of how they were yesterday, before moving the code to the On Format event of the Detail section:
- Access 2016/2019/2021 - the code is working
- Access 2010/2013 - the code is not working - it says something like "There is an invalid use of the . (dot) or ! operator or invalid parentheses", but it says not on which line of code. No problem, these users don't need the charts, they only input data.

So, it seems that there is an unsolved bug in Access 2016/2019/2021, since the same code runs ok in earlier versions of Access, linked to same event (Activate), with same references declared, and now it says "Object doesn't support this property or method" (only on reports, on forms it still runs ok).

Thank you all!

 

[DHookom]

Did you first attempt to compile the code? I don’t think you had

Option Explicit

at the top of your report’s module.

 

[arnelgp]

all variables are declared so compiling won't help.

 

[misterr]

Yes, I compiled every time after changings in code, no errors.

 

[arnelgp]

another, possible solution is to double click on the chart on design view and
manually assign the values there.
Then, remove the code on the Load event of the report (or form).

 

[misterr]

This database is used for PQR (Process Quality Review).

Y(Values) axis on chart can be auto scaled or manual scaled using two text boxes on form, with this code:

Private Sub Detaliu_Format(Cancel As Integer, FormatCount As Integer)

If Not Forms![Produse]![Text16] = Empty Then
    Reports![Grafic]!Grafic0.Axes(xlValue).MinimumScale = Forms![Produse]![Text16]
    Else
        Reports![Grafic]!Grafic0.Axes(xlValue).MinimumScaleIsAuto = True
End If

If Not Forms![Produse]![Text18] = Empty Then
    Reports![Grafic]!Grafic0.Axes(xlValue).MaximumScale = Forms![Produse]![Text18]
    Else
        Reports![Grafic]!Grafic0.Axes(xlValue).MaximumScaleIsAuto = True
End If

End Sub

Sometimes the charts looks good with AutoScale:

Or the Min and max should be adjusted manually by users before printing charts:

I'm affraid that users cannot open the reports in Design View, since they don't have such permissions (user-level security is implemented) and shouldn't do that.
Beside that, it wouldn't be easy for users to do that every time, there are many pages to print.

 

[arnelgp]

so the only way is to put that on the Detail_Format section.
and the Format event will only fire on the PrintPreview view (not on Report view) of your report.
what is xlValue? are you using Excel automation?

you need to change the Default View to Print Preview (in design view).

 

[misterr]

The Default View of reports is already set to Print Preview by design.

Yes, I'm using Excel Automation for some calculation (statistical and so on).

About xlValue on a XY (Scatter) chart:

The X or category axis (the horizontal axis in all charts except the horizontal bar charts) is referenced like this:
ActiveChart.Axes(xlCategory)
You can write "ActiveChart.Axes(1)" instead, it's the same thing.

The Y or value axis (the vertical axis in all charts except the horizontal bar charts) is referenced like this:
ActiveChart.Axes(xlValue)
You can write "ActiveChart.Axes(2)" instead, it's the same thing.

In many kinds of chart you can't change the minimum and maximum axis scale, but in an XY chart you're fine.
The syntax you need is:
ActiveChart.Axes(xlValue).MinimumScale = ...

So, the only charts affected by this bug are the ones in which I'm using the axes properties (MinimumScale, MaximumScale, MinimumScaleIsAuto, MaximumScaleIsAuto).
The others are fine, for example: