Problem with report and subform graph capturing my filter elements

[tmcrouse]

I have a database and a main form that takes information from several listboxes where the user can select whatever they want and then click the button that is click to see results and they will see them in the detail section. I want them to if they want click the button that says click to go to the report and graph. The report portion works fine, however the graph does not. It does not update based on the filter. So, I have 1 query, QualQ1, a main form, then I click the button to get to the report and subform graph. Here is my full code:

Option Compare Database
Option Explicit

Private Sub cmdReport_Click()
'Graph subform is embedded in the QualQ1 report. Cannot do just a graph within report because
'the sizing is not right and now way to alter the actual graph size, just the box that houses
'the entire graph. It does not look right when viewed
DoCmd.OpenReport "QualQ1", acViewPreview, , GetFilterFromListBoxes
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
[\code]

 

[dhookom]

All that code and nothing changes/updates the Row Source of the graph control. Did I miss something?

Duane
Hook'D on Access
MS Access MVP

 

[tmcrouse]

Yeah. I am thinking that somehow I need a function to reference the getfilters to the graph and then call that function when I do the docmd for the report?

 

[dhookom]

I would use the filter information to change the SQL property of a saved query that supplies data to the graph. There is a snippet of code faq701-7433 for changing the SQL.

Duane
Hook'D on Access
MS Access MVP

 

[tmcrouse]

That did not work for me.

 

[dhookom]

That did not work for me.

This does not work for me. I have no idea what you did or what resulted.

Duane
Hook'D on Access
MS Access MVP

 

[tmcrouse]

I tried the SQL query link that you posted and the graph does not update. Here is the issue and going to try making this a bit more simple. I have 3 forms. 1 Main form, 1 form that writes the information from the main form to it and a pivot chart form. I placed the pivot chart form into the form and I have a filter on the Main form. When I click the button on the main form, the activity is set to open the form with subform pivot chart and the results are to update to both. Each alone update fine, however embedded only the form updates which happens to be a continuous form with the pivot chart in header.

This is what I was trying to do as far as the code but think I need to somehow reference the GetFilter from that main form. Just not sure how to do it. Then it should work in theory.

Here is the code:

'Button code in the Main form
Private Sub cmdReport_Click()
DoCmd.OpenForm "frmSQ", acNormal, , GetFilter        'frmSQ is the form that contains the embedded frmPivotGraph
DoCmd.close acForm, "frmMainForm", acSavePrompt    'I want frmMainForm to close upon opening the new form
End Sub
	
'Code in the frmPivotGraph on Open
'Goal is for the GetFilter from frmMainForm to be applied to the PivotGraph embedded in FrmSQ
Private Sub Form_Open(Cancel As Integer)
Dim formfilter As String
formfilter = GetFilter
Debug.Print formfilter
Me.FilterOn = False
Me.Filter = formfilter
Me.FilterOn = True
End Sub

The filter is located in the main module. If I were to code this like this:

'Button code in the Main form
Private Sub cmdReport_Click()
DoCmd.OpenForm "frmSQ", acNormal, , GetFilter        'frmSQ is the form that contains the embedded frmPivotGraph
DoCmd.OpenForm "frmGraph", acPivotChart, , Getfilter

Both open and update just fine, however management does not want 2 separate forms opening. They want 1 with the graph embedded in the form and have it all update like it should. Oddly enough I can see the pivot when embedded in form view and click on the stuff in it and drill-down to where it is a pivot graph of the data in the other part of the form that is correct. But, I need it to do what it is supposed to do when I click the report button on the main form.............

 

[dhookom]

A pivot table or chart has a Record Source. You should be able to change the record source or possibly a saved query that is part of the record source.

I never use pivot tables or charts and don't have much time to test different options and code.

Duane
Hook'D on Access
MS Access MVP