problem with getfilter function for form/subform pivot

[tmcrouse]

I am having quite a time getting my filter function to pass to either a report with embedded pivot chart or form with embedded pivot chart subform to work. Separately, they work fine but I want them to not pull up and show results separately. I am pasting my filter code and what I have now however it pulls them and updates separately and that is not what I want. I have tried so many things playing around with this even including a function for the pivot subform and placing that function in a docmd for the form and tried it in report. I cannot use the regular graphing in Access because it limits to 6 fields and the end-users want ability to alter the pivot chart if needed. So for example they ask for listboxes that grabbed CA and NY and they see the results they can put to a PDF or print and they say wait, I see this pivot graph has NY and CA broken out but I want to be able to send this report to the CA folks and then a separate one to the NY folks so I want to quickly choose CA and send and then go back and choose NY and send. I hope that makes sense. I am going to post my database on here as well as my filter code and what I have now hoping someone can point me in a good direction. I have worked on this about 3wks and nothing I do works right except for each one pulling up separately.

 Option Compare Database
Option Explicit
 Public Function updategraph()
DoCmd.OpenForm "QualityGraph", acFormPivotChart, , GetFilterFromListBoxes
End Function
  
 Private Sub cmdReport_Click()
DoCmd.OpenForm "Search_Quality", acNormal, , GetFilterFromListBoxes
 'DoCmd.OpenForm "QualityGraph", acFormPivotChart,, GetFilterFromListBoxes
updategraph
DoCmd.close acForm, "Search_Quality_Programs", acSavePrompt
End Sub
  
 Private Sub cmdReset_Click()
  Dim ctrl As Access.Control
  Dim itm As Variant
  For Each ctrl In Me.Controls
    If ctrl.ControlType = acListBox Then
      If ctrl.MultiSelect = 0 Then
        ctrl = Null
      Else
        For Each itm In ctrl.ItemsSelected
            ctrl.Selected(itm) = False
        Next
      End If
    End If
  Next ctrl
  Me.Filter = ""
  Me.FilterOn = False
End Sub
  
 Private Sub cmdResults_Click()
   Dim formfilter As String
   formfilter = GetFilterFromListBoxes
   Debug.Print formfilter
   Me.FilterOn = False
   Me.Filter = formfilter
   Me.FilterOn = True
End Sub
 
Public Function GetFilterFromListBoxes() As String
  Dim lst As Access.ListBox
  Dim ctrl As Access.Control
  Dim fieldName As String
  Dim fieldType As String
  Dim TotalFilter As String
  Dim ListFilter As String
  Dim itm As Variant
  'Each listbox needs a tag property with the  field name and the field type
  'Seperate these with a ;
  'The types are Text, Numeric, or Date
  For Each ctrl In Me.Controls
     If ctrl.ControlType = acListBox Then
       fieldName = Split(ctrl.tag, ";")(0)
       fieldType = Split(ctrl.tag, ";")(1)
       For Each itm In ctrl.ItemsSelected
       If ListFilter = "" Then
         ListFilter = GetProperType(ctrl.ItemData(itm), fieldType)
       Else
         ListFilter = ListFilter & "," & GetProperType(ctrl.ItemData(itm), fieldType)
       End If
       Next itm
       If Not ListFilter = "" Then
          ListFilter = fieldName & " IN (" & ListFilter & ")"
       End If
       If TotalFilter = "" And ListFilter <> "" Then
         TotalFilter = ListFilter
       ElseIf TotalFilter <> "" And ListFilter <> "" Then
         TotalFilter = TotalFilter & " AND " & ListFilter
       End If
       ListFilter = ""
     End If
  Next ctrl
  GetFilterFromListBoxes = TotalFilter
End Function
  
 Public Function GetProperType(varItem As Variant, fieldType As String) As Variant
  If fieldType = "Text" Then
    GetProperType = sqlTxt(varItem)
  ElseIf fieldType = "Date" Then
    GetProperType = SQLDate(varItem)
  Else
    GetProperType = varItem
  End If
End Function
  
 Public Function sqlTxt(varItem As Variant) As Variant
  If Not IsNull(varItem) Then
    varItem = Replace(varItem, "'", "''")
    sqlTxt = "'" & varItem & "'"
  End If
End Function
 Function SQLDate(varDate As Variant) As Variant
     If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function