Introducing new and modern chart types

General comments for using the new charting with VBA. (26 Oct.'20, Japan time)

(-) There is a direct answer related to getting the % values to correctly display on a pie chart - without using VBA at the bottom of this post.

1st, I’m not sure how or when new functionality is added. However, I’ve continued to download the latest office 365 whenever I run into an issue when working with Access.

Recently I’ve found that I’m now manually (VBA) able to access more of the chart items then I could a few months ago. I can’t say for sure if it’s just me figuring something out that I didn’t understand before, or if there really has been a change in the underlying coding. I think that at least for some of the issues I’ve run into, there has been some updates that have let me access parts of the charts’ object that I couldn’t before.

A good place to start is at the following Microsoft link:

https://docs.microsoft.com/en-us/office/vba/api/access.chart

And try play around and become comfortable with using the items listed under the “Properties” listing.

Some are simply one dimension structures and others are more complex.

A useful substructure is the, ChartSeriesCollection. This will let you access many of the items you see on the “Property Sheet”.

This is done through the following:

1. Start with a Control object:
1. “Dim objChart As Control”
2. And you set that object to the chart you want to work with.
1. Set objChart = Controls(str_ChartNameYouWantToWorkWith)

You now can access most, if not all, of the items on the chart.

• Simple items examples that I referred to as one dimensional above.

                    objChart.PrimaryValuesAxisRange = glb_AxisRange_Auto

                    objChart.ChartValues = "% of Total"

• Now if you want to access items that are listed under the “Property Sheet” you need to use the ChartSeriesCollection object.

                    objChart.ChartSeriesCollection("Name Of Field Being Graphed").DisplayName = "Revenue"

Now directly to the question concerning showing the % in a pie chart, without using VBA.

First I created a simple Table, “Table1”, as shown below:

Then I created a form with pie charts.

• If you leave everything as default and simply check “Display Data Label” as shown in the above example by IanCampell, the percentages do not display correctly.

However, if you go into Properties / Format and set the “Primary Values Axis Format”, it will display as shown above.

The decimal places shown is determined on how you have formatted it on the table, under “Field Properties”.

May everyone have a pleasant rest of your day and many more.