Access 2007 Pivot Chart VBA Help!

[DerekN]

EDIT: IF you need help programming pivot charts, or pivot tables and automating the creation of them, PM me AND email me, as I have completely resolved this issue and there is not much help available on any forums for Access 2007.


So I know VBA with pivotcharts is kinda sketch. But what I'm looking to do is fairly simple. I want to add multiple Value field values (chDimValues) to my pivot chart.

I have this code.

Private Sub Command62_Click()
'Open Supplier Defects VS # NCRs graph
Dim c As chartspace

Dim objCHR As chartspace    'ChartSpace Object
Dim objCON As Object    'Constants Object
Dim ser As ChSeries


DoCmd.OpenForm "Supplier Defects Graph", acFormPivotChart, , , acFormEdit, acWindowNormal

Set objCHR = Forms("Supplier Defects Graph").chartspace
Set objCON = objCHR.Constants
Set ser = objCHR.Charts(0).SeriesCollection(0)


Select Case Left(Me.defBeginRange.Value, 3)
Case Is = "Jan"
    GoTo Jan
Case Is = "Feb"
    GoTo Feb
Case Is = "Mar"
    GoTo Mar
Case Is = "Apr"
    GoTo Apr
Case Is = "May"
    GoTo May
Case Is = "Jun"
    GoTo Jun
Case Is = "Jul"
    GoTo Jul
Case Is = "Aug"
    GoTo Aug
Case Is = "Sep"
    GoTo Sep
Case Is = "Oct"
    GoTo Oct
Case Is = "Nov"
    GoTo Nov
Case Is = "Dec"
    GoTo Dec
Case Is <> "h"
    GoTo MonthsOver
End Select



Jan:
objCHR.SetData objCON.chDimValues, objCON.chDataBound, "Jan"
If Left(Me.defEndRange.Value, 3) = "Jan" Then
    GoTo MonthsOver
End If
Feb:
objCHR.SetData objCON.chDimValues, objCON.chDataBound, "Feb"
If Left(Me.defEndRange.Value, 3) = "Feb" Then
    GoTo MonthsOver
End If
Mar:
objCHR.SetData objCON.chDimValues, objCON.chDataBound, "Mar"
If Left(Me.defEndRange.Value, 3) = "Mar" Then
    GoTo MonthsOver
End If
Apr:
objCHR.SetData objCON.chDimValues, objCON.chDataBound, "Apr"
If Left(Me.defEndRange.Value, 3) = "Apr" Then
    GoTo MonthsOver
End If
May:
objCHR.SetData objCON.chDimValues, objCON.chDataBound, "May"
If Left(Me.defEndRange.Value, 3) = "May" Then
    GoTo MonthsOver
End If
Jun:
objCHR.SetData objCON.chDimValues, objCON.chDataBound, "Jun"
If Left(Me.defEndRange.Value, 3) = "Jun" Then
    GoTo MonthsOver
End If
Jul:
objCHR.SetData objCON.chDimValues, objCON.chDataBound, "Jul"
If Left(Me.defEndRange.Value, 3) = "Jul" Then
    GoTo MonthsOver
End If
Aug:
objCHR.SetData objCON.chDimValues, objCON.chDataBound, "Aug"
If Left(Me.defEndRange.Value, 3) = "Aug" Then
    GoTo MonthsOver
End If
Sep:
objCHR.SetData objCON.chDimValues, objCON.chDataBound, "Sep"
If Left(Me.defEndRange.Value, 3) = "Sep" Then
    GoTo MonthsOver
End If
Oct:
objCHR.SetData objCON.chDimValues, objCON.chDataBound, "Oct"
If Left(Me.defEndRange.Value, 3) = "Oct" Then
    GoTo MonthsOver
End If
Nov:
objCHR.SetData objCON.chDimValues, objCON.chDataBound, "Nov"
If Left(Me.defEndRange.Value, 3) = "Nov" Then
    GoTo MonthsOver
End If
Dec:
objCHR.SetData objCON.chDimValues, objCON.chDataBound, "Dec"
If Left(Me.defEndRange.Value, 3) = "Dec" Then
    GoTo MonthsOver
End If

MonthsOver:

End Sub

When I would do

objCHR.SetData objCON.chDimValues, objCON.chDataBound, "Jan"

and then another one (lets say Feb)

objCHR.SetData objCON.chDimValues, objCON.chDataBound, "Feb"

Jan is written over and only Feb is added.

 

[DerekN]

UPDATE: I am making progress by editing the pivot table fields and then converting to the chart, but still having problems.

 

[mkunofsky]

DerekN
I know this post is quite old but I stumbled on it in a vain search of knowledge. Your top line says that you have resolved the issue. If you are still monitoring this site and could enlighten me I would be ever grateful.

I have a distributed application that uses a number of different pivot tables. At present they open just fine except that they open in the configuration left by the previous user. I want them to open to a predetermined state each time.

The only information I can find is for Access 2002 which requires a reference OWC10.dll which doesn't seem to exist anymore and there is no information for Access 2007. (as you noted).

Any help is appreciated. Thanks