filter pivot query/chart with vba

Question

Thanks for taking the time to read my question.

I followed these instructions https://www.youtube.com/watch?v=iJ4j4Yd8pdA and made a form that has a PivotChart view as a subform. The PivotChart shows spend per category over time.

I am opening the form with the PivotChart view from another form and I'd like to send a category or categories in the OpenArgs to the PivotChart form and filter it based on the OpenArgs.

On my PivotChart I have the option: "Field buttons / drop zones" checked. This allows me to filter on Category or Date when the PivotChart is visible. My guess is I have to tell the Category filter to select the category or categories  to check.

I'm not even sure where to begin so any help would be great.

Thanks,
Brad

Answer 1

Just a suggestion (not the fix), but since Access is basically an MS Office application, you can use another MS Office application that has a little more support for pivot tables -- ie -- Excel (on the front end that is).  Try creating some pivot tables in Excel which I find a bit easier than Access.  Once you have a little more time in grade with pivot tables in Excel you can migrate that to  Access .

---

Rich P

Answer 2

Hi Brad,

You could custom Form_Open event for the form with the PivotChart view to catch the OpenArgs.

For accessing and filtering the PivotTable Object, you could refer to below link.

Programming Pivot Tables for Access Forms

Here is a simply code for my data.

PS:Remeber to add reference to Office Web Components library(OWC11.dll)

Click Event for openning the form.

Private Sub Command11_Click()

DoCmd.OpenForm FormName:="Form1", OpenArgs:="Boston"

End Sub

Form Open event for the Form with PivotTable View

Private Sub Form_Open(Cancel As Integer)

filterStr = Me.OpenArgs

Dim SBF As Form

Set SBF = Me.Query1.Form

Set ptv = SBF.PivotTable

Set FLD = ptv.ActiveView.FieldSets("Location").Fields("Location")

FLD.IncludedMembers = filterStr

End Sub

Best Regards,

Terry

---

MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 3

Hi Terry,

Thank you very much for your reply.

I have this so far:

Private Sub Form_Open(Cancel As Integer)
Dim tCat() As String

If Nz(Me.OpenArgs, "") <> "" Then
    tCat() = Split(Me.OpenArgs, ",", , vbTextCompare)
    For i = LBound(tCat) To UBound(tCat)
        Debug.Print tCat(i)
    Next
    
    For Each ctrl In Me.qryCategorySpend_Chart.Form.Controls
        Debug.Print ctrl.Name
        Debug.Print ctrl.Value
    Next
End If
End Sub

I can get the Detail filter (CAD) and a value, the Category filter and a value and Date filter and a date

These are from the first For Each
Car Gas
Car Maint

These are from the first For Each
CAD
 234.96
Category
Misc
Date
03-2017

I was wondering if it's possible to treat it like a ListBox or ComboBox as I've read that the OWC11.dll is being depreciated or something... no longer shipping with Office as MS needs a newer way to do things... maybe that's not true?

I'll try it and use your example code and see how things go.

Thanks Terry,

Brad

Answer 4

Hi Terry,

Here is what I have tried so far, without success.

Private Sub Form_Open(Cancel As Integer)
'This is needed for the code below to run: https://www.microsoft.com/en-us/download/details.aspx?id=22276 OWC11.dll
Dim tCat() As String

If Nz(Me.OpenArgs, "") <> "" Then
    tCat() = Split(Me.OpenArgs, ",", , vbTextCompare)
    For i = LBound(tCat) To UBound(tCat)
        Debug.Print tCat(i)
    Next
    
'    For Each ctrl In Me.qryCategorySpend_Chart.Form.Controls
'        Debug.Print ctrl.Name
'        Debug.Print ctrl.Value
'    Next
    
    filterStr = Me.OpenArgs
    
    Dim SBF As Form
    
    Set SBF = Me.qryCategorySpend_Chart.Form
    
    Set ptv = SBF.PivotTable
    
    Set FLD = ptv.ActiveView.FieldSets("Category").Fields("Category")
    
    'FLD.IncludedMembers = filterStr
    'FLD.IncludedMembers = Array(filterStr)
    FLD.IncludedMembers = tCat()


End If
End Sub

I'm not quite sure what I'm doing incorrectly.

OpenArgs = ""Car Gas","Car Maint""

I have put it into an array tCat() also.

I am focusing on the code just above the CONCLUSION section in the URL you provided. I changed ShipCountry to Category, which is the name of the control in my PivotChart.

When I run the code, my PivotChart displays with no data, and no category values are selected and the "All" is grayed out.

Thanks,
Brad

Answer 5

Hi Terry,

I have it working... sort of...

Private Sub Form_Open(Cancel As Integer)
'This is needed for the code below to run: https://www.microsoft.com/en-us/download/details.aspx?id=22276 OWC11.dll
Dim tCat() As String
Dim test As Variant
tfilterstr = ""

If Nz(Me.OpenArgs, "") <> "" Then
    tCat() = Split(Me.OpenArgs, ",", , vbTextCompare)
    For i = LBound(tCat) To UBound(tCat)
        'Debug.Print tCat(i)
        If tfilterstr = "" Then
            tfilterstr = tCat(i)
        Else
            tfilterstr = tfilterstr & ";" & tCat(i)
        End If
    Next
    
'    For Each ctrl In Me.qryCategorySpend_Chart.Form.Controls
'        Debug.Print ctrl.Name
'        Debug.Print ctrl.Value
'    Next
    
    filterStr = Me.OpenArgs 'This over rides the for loop above
    'filterStr = "Car Gas, Car Maint"

    Dim SBF As Form
    
    Set SBF = Me.qryCategorySpend_Chart.Form
    
    Set ptv = SBF.PivotTable
    
    Set FLD = ptv.ActiveView.FieldSets("Category").Fields("Category")

    
    'FLD.IncludedMembers = filterStr 'Doesn't work
    'FLD.IncludedMembers = tCat() 'Doesn't work
    'FLD.IncludedMembers = Array(filterStr) 'Doesn't work
    FLD.IncludedMembers = Array("Car Gas", "Car Maint") 'Works!


End If
End Sub

If I manually put values in the Array then it works. I can't seem to get anything else to work. How do I get my values in there properly?

Thanks,
Brad

Answer 6

Hi Terry,

I have come up with a REALLY BAD WAY to make this work using UBOUND and SELECT CASE and creating 60 cases (starting a zero) to make the Array() function work.

In the case where there are more than 60, I want to just have the Pivot Chart show all.

This is not working: FLD.ExcludedMembers = Array("")

Should it?

Thanks,
Brad

Answer 7

Hi mbrad,
->OpenArgs = ""Car Gas","Car Maint""
You added unnecessary double quotation marks in OpenArgs, please try 
OpenArgs = "Car Gas,Car Maint"
Best Regards,
Terry

---

MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 8

Hi Terry,

thanks for your reply. That is was I was doing originally and tried the double quotes around each item as well... which also doesn't work.

The only think I could get to work was to use the Array() function. To do this I have to put my string into an array, then use a SELECT CASE UBound(myarray()) to get the size of the array, then go to the CASE. I then do

CASE = 3
Array(myarray(0), myarray(1),myarray(2))
CASE = 4
Array(myarray(0), myarray(1),myarray(2),myarray(3))
.....

this is the only thing that has worked.

Brad

Answer 9

Hi mbrad,

As IncludedMembers Property said "This property can be set to a single member or a Variant array of members." However, the Split Function could only return a string array. We need to convert the string array to a variant array.

Here is a workaround exporting data in the string array to another variant array.

Dim arr() As Variant

ReDim arr(LBound(tCat) To UBound(tCat))

For I = LBound(tCat) To UBound(tCat)

arr(I) = tCat(I)

Next I

FLD.IncludedMembers = arr

Best Regards,

Terry

---

MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 10

Hi Terry,

That worked perfectly! I did try changing my tCat to a Variant instead of string but I guess the Split made it string...

Thank you so very much. now my code is not static and bloated.

Have a great day, and thanks again for your help.

Brad