PivotChart form adding new series with VBA

[RandomCoder]

[SOLVED] PivotChart form adding new series with VBA

I had hoped that my first post would be a solution on how to utilise the OWC10 chartspace component but I've run into problems that I'm seemingly unable to resolve.

I've exhaust all the web resources I can find and overcome many problems such as using OWC10 instead off OWC11 as it doesn't appear to play nicely with Access 2003 when explicitly declared (if not explicitly declared then the intelisense feature doesn't work!).

Anyway, please the the attached example of what I have achieved so far. If you open the frmMaintAnalysis form I am wanting to add another data series to the existing one, which can easily be accomplished using the mouse by...

Right clicking in the chart area.
Select field list
Then drag the "Breakdowns_Elec" field into the chart.
Simples (but not for some of my users)!
​

But how to achieve this using VBA? I'd like to use the "Add" button to add another series to the chart, selected using the combobox. I've spent a week getting this far as the web components help is enough to drive a person insane.
One useful tip that I've learnt whilst trawling the web is that changing the pivotforms record source has the effect of wiping the chartspace and so I do this when the form loads. I then set the chartspace data using setdata method but after that I'm unable to add any other data. If I select literal data instead of bound data then the series is added but with no values showing.

Am I being really stupid, as I feel that the answer is staring me in the face but I'm unable to see it? I'm posting here out of desperation, please kindly offer me some advice, it will save me even more hair loss!

Thanks in advance,
RandomCoder

PS. This is a work in progress, the code still needs some tidying up!

 

[RandomCoder]

[SOLVED] PivotChart form adding new series with VBA

I've finally found the answer to my problem...

    Dim p As PivotTable
    Dim pv As PivotView
    'Assign PivotView to variable pv
    Set pv = c.Charts(0).Axes(0).CategoryLabels.PivotAxis.GroupFields(0).Axis.Data.View
    'Insert the new fieldset from the already bound datasource
    pv.DataAxis.InsertFieldSet pv.FieldSets("Breakdowns_Mech")
 
    ' Create a total named "Sum of Breakdowns_Mech" that counts the Breakdowns_Mech field.
    Set newtotalfield = pv.AddTotal("Sum of Breakdowns_Mech", pv.FieldSets("Breakdowns_Mech").Fields("Breakdowns_Mech"), plFunctionSum)
 
    ' Add the Breakdowns_Mech Count total to the data axis.
    pv.DataAxis.InsertTotal newtotalfield
 
    'Assign pivot table to variable p
    Set p = c.InternalPivotTable
    'Insert both datafields into the data axis
    p.ActiveView.DataAxis.InsertFieldSet p.ActiveView.FieldSets("Breakdowns_Mech")
    p.ActiveView.DataAxis.InsertFieldSet p.ActiveView.FieldSets("Breakdowns_Elec")
    'And the key ingredient to get it to plot all series...
    c.PlotAllAggregates = chPlotAggregatesSeries

I'll post a complete working axample once I've tidied up the code and got it all working. This has been driving me mad, I've gone round in circles so many times that I'm feeling quite queezy :banghead:

It turns out that the key to most of my problems was that I needed to use chartspace.plotallaggregates to get the chart to display all selected data series. For some reason this didn't appear to be the case when plotting literal data, as I've followed several examples without any problems but as soon as I attempted to use data stored in my table it all went horribly wrong.

Regards,
RandomCoder.

 

[RandomCoder]

[SOLVED] PivotChart form adding new series with VBA

As promised a working sample as I've finally got my SubForm PivotChart to work as required.
Points to note....

- This works on Access 2003, it may work on other versions but I'm unable to test.
- Uses OWC10 Web Components library reference, OWC11 gave me a headache!
- There's currently no error trapping (this is just my working example prior to inserting into an actual project).
​

Through scouring the internet I've discovered many useful things, most noticeably...

- Reloading the RecordSource for the PivotChart Form clears the ChartSpace object whilst setting its bound DataSource.
- Even after creating a new dataseries total it will not be displayed unless you command the ChartSpace to PlotAllAgregates
- Printing the SubForm on its own is tricky but not impossible. You need to issue a save command and then open the PivotChart Form in its printpreview mode.
​

I'm self-taught using VBA and so there's probably plenty of things that could be done a better way than in my attached example, but as I've found very few working examples whilst searching the internet and considering the amount of posts I've found asking the same questions I had then I'm hoping this will be of some use to someone else.

Please leave constructive comments that will help me to further improve my code.

Regards,
RandomCoder

 

[ions]

Hi RandomCoder

Can you check out my PivotChart VBA question below? I have downloaded your solution and will also look for the answer there.

Thank you

http://www.access-programmers.co.uk/forums/showthread.php?t=257010

 

[ions]

RandomCoder,

I am just looking at your solution. I am running it in Access 2007. The functionality is very good and the code is very well documented.

Thank you for providing this solution. I don't believe you implemented the feature I am looking for in your example but I will keep studying your code.