How to change the RecordSource of an Access 2010 PivotChart-Form

by Philipp Stiefel, originally published June 27^th, 2017

Recently someone asked a question about an Access 2010 Form in PivotChart-View (in German language). He wanted to display the same chart for different business entities. To achieve that he tried to change the data source for the PivotChart-Form at runtime using VBA. An important detail in the scenario was that the Access application was and ADP-Project with SQL-Server-Data.

This question had several interesting aspects for me. The first one being that there is a genuine and tough problem with that. Furthermore, research revealed this problem had been brought up a few times on different VBA-, Access-, and Excel-Forums on the web but it was never fully resolved. And finally, none of the regulars on Access-O-Mania knew much about the PivotChart-View of Access Forms at all.

This is reason enough to look at this issue in more depth. However, as the involved technology is deprecated and rarely used, I will only provide some pointers to solutions instead of a complete guide on this topic.

Access 2010 Forms in PivotChart-View

In Access 2007 and 2010 Microsoft introduced a new PivotTable- and PivotChart-View for Access Forms. Particularly the PivotChart-View was an easy option to visually display the data from your Access database without the need for any external components.

In many scenarios it is a bonus that the data can be easily filtered for drilldown operations. Additionally users can fairly easily change the fields for the chart axes, the data fields and even the chart type.

There is a video showing PivotTable- and PivotChart-View in Access 2010 on the Office-Support-Website.

However, Microsoft decided to remove this feature again with Access 2013. It was not used much and even of you still use Access 2010 today, I advise against using it for any major new development, as there is no way forward for this technology.

The problem with changing the RecordSource

As mentioned above, it is easy for experienced users to filter the data in the chart. This is may be sufficient in many cases. If you want to apply more complex filters to the data, you’ll probably rather resort to VBA and SQL programming instead of letting the user (try to) set these filters. - That’s were the problem is.

Usually you can easily change the data source of any Access Form in VBA just by changing the RecordSource-Property. You assign a new query, table, or SQL-Statement to this property. As long as the field names in the new data source are the same as before, the data from the new source is instantly displayed in the form. - Trivial.

Unfortunately, with a form in PivotChart-View it is not so trivial. Whenever you change the RecordSource-property of the form, the data field properties of PivotChart are completely reset. It loses all the category, series, data, and filter field settings.

It is irrelevant when the RecordSource is changed. Even if you use VBA code in the Form_Load- or Form_Open-Events to change the RecordSource, the chart is still reset.

Solution 1 - Parameter Queries / Stored Procedures

If you want to reuse the same query with different parameters for filtering the data, there is a fairly simple solution to the problem. In an Access ACCDB-database you can use a parameter query referencing controls in another form or custom VBA functions to retrieve the parameter values each time you open the form. This is pretty much bog standard and I will not go into more detail here.

However, the scenario of the original question is an ADP-Project with SQL-Server data. There are no local queries and the SQL-Server-Views and Stored Procedures cannot access any controls from the user interface nor any VBA functions.

A similar approach in an ADP is to use a Stored Procedure and the not very well known InputParameters-Property of the form. Unfortunately, if you just modify the InputParameters with VBA, it has the exact same effect as modifying the RecordSource directly and the chart is reset. But InputParameters allow for references to UI controls and custom VBA functions.

The basic syntax for InputParameters is:

@FirstParameterName paramDataType = paramValue, @SecondParameterName paramDataType = paramValue

Here are some example uses of the InputParameters-property for an Int parameter.

• Setting a static parameter value

  @anIntParam int = 999

• Referencing a control on an Access Form

  @anIntParam int=forms!frmYourParameterForm!ParamValueControl

• Referencing a public VBA function

  @anIntParam int = YourFunctionName()

Here are screenshots of an actual TSQL-Procedure and the properties used in the Access form in an ADP to call it.

This will solve most cases of this problem. You should be aware of the fact that parameters in a local query as well as the InputParameters for a Stored Procedure are evaluated only once when the form is opened. There is no (easy) way to requery the data once the form is open.

Solution 2 - Programming the PivotChart-View with VBA

The above solution works whenever the actual RecordSource of the form is not changed and only different parameter values are applied to query the data. That will be sufficient in most cases.

There may be some rare cases where it is actually required to change the RecordSource to totally different table or query. Then the above approach will obviously not help at all.

The obvious solution would be to set the required properties to data bind the chart with VBA. The problem with that is the PivotTable- and PivotChart-View of the Access Form seem not to expose much of an API to set any of its properties in VBA. The relevant properties of the form are the ChartSpace or the PivotTable properties. But both are just of type Object and there is no intellisense nor any further information in the VBA Object Browser.

The ChartSpace and PivotTable-Objects are implemented in the Microsoft Office Web Components library. The library file is OWC11.DLL and it is usually located in the folder C:\Program Files (x86)\Common Files\Microsoft Shared\Web Components\11\.

You can add a reference to this library to view all the properties and methods of the ChartSpace-Object in the VBA Object Browser.

Although this library is deprecated, the documentation of the Office Web Components API is still available.

This API enables to you to control many aspects of the PivotChart-View (or PivotTable-View) using VBA. Our main focus here is to solve the problem with the reset data binding. So, I will only show how to set the relevant properties for this.

You do not need to set a reference to the OWC-Library to use this code when using Late Binding. Even if you want to consume events from the library as most of those are available on the Access Form Object directly.

To set the data binding of the chart, we just call the SetData Method. When using late binding, I recommend re-declaring the required Enums in your code. I am lazy and only include the enum values I actually use in my tiny example.

Here is a short sample showing how to set up a ChartSpace with Categories, Series, and Values. (ActorName, RatingYear and Rating are field names in my Stored Procedure.)

Me in the sample code references the form in PivotChart-View.

Conclusion

The PivotChart-View and PivotTable-View in Access have always been some kind of unloved step child of Access. They were not promoted much until they were finally abandoned with Access 2013. Still, they were useful for data visualisation.

Based on the information from this article you can control these Views with VBA code, which significantly extends their usefulness. Unfortunately, nowadays this only makes sense for smaller projects with little programming work required, as you will need to rewrite this code when updating to Access 2013 or newer.

In Access 2013 there is a Microsoft Graph Chart control built into Access, which can be used to visualize data in a similar way.

Share this article: