Using VBA to set up a query in acViewPivotChart

[Wyze]

Using MS Access 2007 VBA once I have opened a query in PivotChart view with
the following:

DoCmd.OpenQuery "MyQuery", acViewPivotChart

How can I set the following:

1) Filter
2) Data
3) Series
4) Category

The user can "Drag & Drop" items from the popup Field List.

How can I complete this using VBA?

 

[Wyze]

Here is a solution to my situation that I have been able to piece together
and get to a rough state using a single form rather than multiples and I do
NOT have to use the OWC reference to make it work. It is incomplete and still
under construction but it may help somebody someplace as it would have helped
me had I been able to find anything closely resembling the following. I do
not know if I will ever be able to get it to work directly with a query in
PivotChart view but I do have it working with a form in PivotChart view
created at run time or it could be used with a previously created form used
over and over. At the very least it is a great starting place for anybody
trying to acomplish what I have spent so many hours searching for. Enjoy!

Note: I am currently researching how to set the Filter "DropZone" data
column value and Series label text value.

Private Sub RunSolution()

Dim objOBJ As AccessObject

For Each objOBJ In CodeProject.AllForms
If objOBJ.Name = "Form1" Then
DoCmd.DeleteObject acForm, "Form1"
Exit For
End If
Next

Dim frmFRM As Form
Set frmFRM = CreateForm
frmFRM.DefaultView = 4
frmFRM.RecordSource = "qryTEST"
DoCmd.Close acForm, frmFRM.Name, acSaveYes
Set frmFRM = Nothing
Set objOBJ = Nothing

Call CreatePivotChart("Form1", "qryTEST", "Category", "Total")

End Sub

Public Function CreatePivotChart( _
ByRef strFormName As String, _
ByRef strQueryName As String, _
ByRef strColumnCats As String, _
ByRef strColumnVals As String)

On Error GoTo CreatePivotChart_Error

Dim objCHR As Object 'ChartSpace
Dim objSER As Object 'Series
Dim objSEG As Object 'Segment
Dim objCON As Object 'Constants
Dim objDZN As Object 'DropZone
Dim objPLA As Object 'PlotArea

DoCmd.OpenForm strFormName, acFormPivotChart, , , , acWindowNormal

Set objCHR = Forms(strFormName).ChartSpace
Set objCON = objCHR.Constants
Set objPLA = objCHR.Charts(0).PlotArea
Set objSER = objCHR.Charts(0).SeriesCollection(0)
Set objSEG = objSER.FormatMap.Segments.Add
Set objDZN = objCHR.DropZones(objCON.chDropZoneSeries)

'*** DIFFERENT CHART OPTIONS ***
'chChartTypeArea
'chChartTypeArea3D
'chChartTypeAreaOverlapped3D
'chChartTypeAreaStacked
'chChartTypeAreaStacked100
'chChartTypeAreaStacked1003D
'chChartTypeAreaStacked3D
'chChartTypeBar3D
'chChartTypeBarClustered
'chChartTypeBarClustered3D
'chChartTypeBarStacked
'chChartTypeBarStacked100
'chChartTypeBarStacked1003D
'chChartTypeBarStacked3D
'chChartTypeBubble
'chChartTypeBubbleLine
'chChartTypeColumn3D
'chChartTypeColumnClustered
'chChartTypeColumnClustered3D
'chChartTypeColumnStacked
'chChartTypeColumnStacked100
'chChartTypeColumnStacked1003D
'chChartTypeColumnStacked3D
'chChartTypeCombo
'chChartTypeCombo3D
'chChartTypeDoughnut
'chChartTypeDoughnutExploded
'chChartTypeLine
'chChartTypeLine3D
'chChartTypeLineMarkers
'chChartTypeLineOverlapped3D
'chChartTypeLineStacked
'chChartTypeLineStacked100
'chChartTypeLineStacked1003D
'chChartTypeLineStacked100Markers
'chChartTypeLineStacked3D
'chChartTypeLineStackedMarkers
'chChartTypePie
'chChartTypePie3D
'chChartTypePieExploded
'chChartTypePieExploded3D
'chChartTypePieStacked
'chChartTypePolarLine
'chChartTypePolarLineMarkers
'chChartTypePolarMarkers
'chChartTypePolarSmoothLine
'chChartTypePolarSmoothLineMarkers
'chChartTypeRadarLine
'chChartTypeRadarLineFilled
'chChartTypeRadarLineMarkers
'chChartTypeRadarSmoothLine
'chChartTypeRadarSmoothLineMarkers
'chChartTypeScatterLine
'chChartTypeScatterLineFilled
'chChartTypeScatterLineMarkers
'chChartTypeScatterMarkers
'chChartTypeScatterSmoothLine
'chChartTypeScatterSmoothLineMarkers
'chChartTypeSmoothLine
'chChartTypeSmoothLineMarkers
'chChartTypeSmoothLineStacked
'chChartTypeSmoothLineStacked100
'chChartTypeSmoothLineStacked100Markers
'chChartTypeSmoothLineStackedMarkers
'chChartTypeStockHLC
'chChartTypeStockOHLC
'****************************************

objCHR.Charts(0).Type = objCON.chChartTypeColumnClustered3D

objPLA.BackWall.Interior.SetSolid "LightGray"
objPLA.BackWall.Thickness = 5
objPLA.SideWall.Interior.SetSolid "LightGray"
objPLA.SideWall.Thickness = 5
objPLA.Floor.Interior.SetSolid "LightGray"
objPLA.Floor.Thickness = 5

' Legend
objCHR.HasChartSpaceLegend = True
objCHR.ChartSpaceLegend.Position = objCON.chLegendPositionTop

' Axes Title (No thanks!)
objCHR.Charts(0).Axes(0).HasTitle = False
objCHR.Charts(0).Axes(1).HasTitle = False

' Categories
objCHR.SetData objCON.chDimCategories, objCON.chDataBound, strColumnCats

' Values
objCHR.SetData objCON.chDimValues, objCON.chDataBound, strColumnVals
'objCHR.SetData objCON.chDimFormatValues, objCON.chDataBound,
strColumnVals

' Add Segment and create divisions in formatting automatically
' with the segment boundaries measured using percentage between
' 0 & 1 and displaying shades of Blue through shades of Red
objSEG.HasAutoDivisions = True
objSEG.Begin.ValueType = objCON.chBoundaryValuePercent
objSEG.End.ValueType = objCON.chBoundaryValuePercent
objSEG.Begin.Value = 0
objSEG.End.Value = 1
objSEG.Begin.Interior.Color = "Blue"
objSEG.End.Interior.Color = "Red"

' Custom color the Dropzone
objDZN.ButtonBorder.Weight = objCON.owcLineWeightMedium
objDZN.ButtonInterior.SetSolid "LightGray"
objDZN.ButtonFont.Size = 14
objDZN.WatermarkBorder.Color = "LightGray"
objDZN.WatermarkFont.Color = "LightGray"
objDZN.WatermarkInterior.SetSolid "Blue"

Set objCHR = Nothing
Set objSER = Nothing
Set objSEG = Nothing
Set objCON = Nothing
Set objPLA = Nothing

CreatePivotChart_Exit:
Exit Function

CreatePivotChart_Error:
MsgBox Err.Description
Debug.Print Err.Description
Err.Clear
Resume CreatePivotChart_Exit

End Function

 

[Wyze]

OK So now I have a few more items to research for this code to do what I want
it to do:

1) How to load the Field List values?
2) How to set the Filter "Drop Zone" column names?
3) How to manipulate that series dropzone better so that it is not just the
value data?

Private Sub CreatePivotChart()

On Error GoTo CreatePivotChart_Error

Dim objCHR As Object 'ChartSpace Object
Dim objSER As Object 'Series Object
Dim objSEG As Object 'Segment Object
Dim objCON As Object 'Constants Object
Dim objDZN As Object 'DropZone Object
Dim objPLA As Object 'PlotArea Object

Dim strFRM As String 'Form Name
Dim strQRY As String 'Query Name
Dim strCAT As String 'Category Column Name
Dim strVAL As String 'Value Column Name
Dim strFIL As String 'Filter Column Name

Dim frmFRM As Form 'Temp Form
Dim objOBJ As AccessObject

strFRM = "Form1" 'Form Name
strQRY = "qryTEST" 'Query Name
strCAT = "Category" 'Category Column Name
strFIL = "Group" 'Filter Column Name
strVAL = "Total" 'Value Column Name

' If temp form exists delete it!
For Each objOBJ In CodeProject.AllForms
If objOBJ.Name = strFRM Then
DoCmd.DeleteObject acForm, strFRM
Exit For
End If
Next

' Create new temp form
Set frmFRM = CreateForm
frmFRM.DefaultView = 4
frmFRM.RecordSource = strQRY
DoCmd.Close acForm, strFRM, acSaveYes
Set frmFRM = Nothing
Set objOBJ = Nothing

' Open new temp form
DoCmd.OpenForm strFRM, acFormPivotChart, , , , acWindowNormal

' Work with the ChartSpace object for our PivotChart view!
Set objCHR = Forms(strFRM).ChartSpace
Set objCON = objCHR.Constants

' Category
objCHR.SetData objCON.chDimCategories, objCON.chDataBound, strCAT

' Value
objCHR.SetData objCON.chDimValues, objCON.chDataBound, strVAL

' Filter???
'Need a line of code here to set filter column value(s)

Set objSER = objCHR.Charts(0).SeriesCollection(0)
Set objSEG = objSER.FormatMap.Segments.Add
Set objPLA = objCHR.Charts(0).PlotArea
Set objDZN = objCHR.DropZones(objCON.chDropZoneSeries)

' Chart Type
objCHR.Charts(0).Type = objCON.chChartTypeColumn3D

' Show Legend
objCHR.HasChartSpaceLegend = True
objCHR.ChartSpaceLegend.Position = objCON.chLegendPositionTop

' Load Field List
'Need a line of code here to load Field List column names

' Hide Field List
objCHR.DisplayFieldList = False

' Hide Axes Title
objCHR.Charts(0).Axes(0).HasTitle = False
objCHR.Charts(0).Axes(1).HasTitle = False

' Hide Series Dropzone Label / Button
objDZN.ButtonInterior.SetSolid "White"
objDZN.WatermarkBorder.Color = "White"
objDZN.WatermarkFont.Color = "White"
objDZN.WatermarkInterior.SetSolid "White"

' Add Segment and create divisions in formatting automatically
' with the segment boundaries measured using percentage between
' 0 & 1 and displaying shades of Blue through shades of Red
objSEG.HasAutoDivisions = True
objSEG.Begin.ValueType = objCON.chBoundaryValuePercent
objSEG.End.ValueType = objCON.chBoundaryValuePercent
objSEG.Begin.Value = 0
objSEG.End.Value = 1
objSEG.Begin.Interior.Color = "Blue"
objSEG.End.Interior.Color = "Red"

CreatePivotChart_Exit:
Set objCHR = Nothing
Set objSER = Nothing
Set objSEG = Nothing
Set objCON = Nothing
Set objPLA = Nothing
Exit Sub

CreatePivotChart_Error:
MsgBox Err.Description
Debug.Print Err.Description
Err.Clear
Resume CreatePivotChart_Exit

End Sub