Report with embedded subform pivot chart

[tmcrouse]

I am having a terrible time getting this to work. I have a mainform that contains 11 multi-list boxes. That mainform when I select whatever I want in any of the 11, select all items in each of the 11, or select nothing and click my show results works great. In my detail section it displays the information it should. I need to now take that and put it in a report with a pivot graph. I created a subform that is my pivotgraph and the reason for a subform is the end-user still wants the ability to filter more should they choose. In addition, the regular graph you can put in the report does not allow me to put in multiple items. For instance I need to have sumofmbrstargeted and sumofmbrsconverted in the values and it will say I can only choose 1 value and then it will say up to 6 items and I have a 8. So, I opted for a subformpivotchart. If I create the following code, the subform updates based on the filters on this mainform everytime. Works like a charm.

Private Sub cmdGetGraph_Click()
DoCmd.OpenForm "Graph", acFormPivotChart, , GetFilterFromListBoxes
End Sub

The problem is, I want the Graph in my Report. If I take the Report and do the same basic thing:

Private Sub cmdGetReport_Click()
DoCmd.OpenReport "Search", acViewPreview, , GetFilterFromListBoxes
End Sub

It asks for the parameters again. Example, LOB, Plan, Prod_NM. I tried just disregarding the report and created yet another subform for the reporting piece and embedded the graph and that still asked for the parameters. It works great for the subform as the graph and I want to have the ability to use the GetFilterFromListBoxes, apply it to the Search Report with the embedded Graph.

[June7]

The wizard does have limits but once object is created can do edits that ignore those limits. However, I am not sure what you mean by '6 items'.

I analyze graph issues best when I can work with data. If you want to provide db, follow instructions at bottom of my post.

I have never used pivot forms/tables. Be aware, MS has removed that feature from Access 2013.

[tmcrouse]

I have a main form that filters information and then displays that information in the details. I am trying to take that displayed information and put it in a report so it is nice and neat and then put a graph that sums the memberstargeted and membersconverted based on criteria that I will attach with my database. The graph works great alone when clicking the button from the mainform to go to the graph. It does what it should, however when I try and do anything with the report, it wants me to re-enter the parameters. Then when I just bypass all of that by clicking ok and placing nothing in there, I can view the report with embedded graph, and the report is showing what it did from mainform but graph is not updated. The goal is to just go from the mainform to the subreport/with subform graph by clicking the button on the mainform. I am pasting my code and my zipped DB

Option Compare Database
Option Explicit
Private Sub cmdReport_Click()
DoCmd.OpenForm "graph", acFormPivotChart, , GetFilterFromListBoxes
DoCmd.OpenReport "search_programs", 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

Any help would be fantastic cuz I have been going insane trying to figure this out.

[June7]

Those popup prompts have nothing to do with the graph. I deleted the subreport and the main report still prompts for the inputs. Doesn't make sense because the RecordSource does not have filter parameters, has only one calculated field, and the report has no code in any of its events, and I made sure the Filter property is blank. I created another report with QualQ1 as RecordSource and it opens without prompts. I added the Graph form as subreport and the report still opens without prompts. Only thing I can think of is corrupted report.

[tmcrouse]

I will try that and see what happens.

[tmcrouse]

Ok. I created it from scratch and the popups do not occur but I have the subform that is a pivotgraph in the details of the report and it does not pick up the filter. If I just have a command button and put only report it works great. If I remove that and point to subform with pivotgraph, works great. But when I put them together, does not work. The pivotgraph does not display the results.

[June7]

The filter string returned by the GetFilterFromListBoxes function is not applied to the subform.

Can try calling the function from Graph form Filter property. Referencing procedures behind forms from another module is tricky. Try:

Forms!Search_Quality_Programs.GetFilterFromListBox es()

Set FilterOnLoad property to Yes.

Ouch, that pops up input prompt.

Other option is a public function in a general module that can be called from any location. The Graph form RecordSource could be a query object or SQL statement that references function in WHERE clause.

Never seen anything like this. I've never used pivot chart/table/form. For a report, I would build a graph object. However, still an issue of getting the filter criteria from the function applied to the SQL in the graph RowSource property.

[tmcrouse]

The GetFilterFromListBoxes is in the general module and a public function. How do I do a query object in the recordsource or sql statement that references function in the where clause

[June7]

I revisited your original db posted. Found what was causing the popups. They were caused by the sorting parameters set up by Sorting & Grouping builder. I removed them and no more popups.

This version does not have the function code in general module. So I did a quick test of setting report Filter property of both report and subreport/form with another function: [PROG_NM]=GetFilter()

Function GetFilter()
GetFilter="Live OBC"
End Function

The main report is correctly filtered but the subreport/form is not. However, as you already discovered, opening the Graph form independently does apply the filter. I know it is possible to use code to set a subform Filter property because I do that but with subform on a form, not a report. Never tried to set Filter property of subreport. All my subreports synchronize with main report by Master/Child Links.

I changed the subreport/form to Datasheet default. I changed the Filter property for both to: [PROG_NM]<>GetFilter(). Again, the main report is filtered but not the subreport/form.

Then I tried the Graph form as a subform on a form (not report) and WOW, the filter works for both!

Last test, I put the function in QualQ1 as criteria under PROG_NM field: <>GetFilter(). Both report and subreport/form are filtered.

I think your options are:

1. use graph control and set RowSource property

2. dynamic parameter in query object under each field that references control on form

3. code using QueryDefs to modify the query object

[tmcrouse]

Ok is there a book that says how to do this? I am not an Access developer. I know SAS and the 2 languages are like night and day except for SQL. This VB stuff I just don't get and I do not understand what I am to do because I tried what you said and it does not work because the filter is from another form and the filter name is GetFilterFromListBoxes. I am not sure what put the function of QualQ1 means because QualQ1 is a query. I am utterly lost.

[tmcrouse]

I am just going to hire someone to do this. 60 bucks is worth getting rid of this 3wk frustration and no sleep.

[June7]

All I did in the QualQ1 query was reference the function in the Criteria row: =GetFilter() or <>GetFilter()

You will have to put function in a general module, not behind another form.

However, all my example function can do is set the criteria for a single field, not multiple fields.

Did you try option 2?

There are lots of books on programming for Access. You might find this one of interest: Access™ 2007 Programming by Example with VBA, XML, and ASP by Julitta Korol, Wordware Publishing, Inc.

[ItsMe]

Ok is there a book that says how to do this? I am not an Access developer. I know SAS and the 2 languages are like night and day except for SQL. This VB stuff I just don't get and I do not understand what I am to do because I tried what you said and it does not work because the filter is from another form and the filter name is GetFilterFromListBoxes. I am not sure what put the function of QualQ1 means because QualQ1 is a query. I am utterly lost.

I did not read the whole thread but, you need to understand that a form's module is Private to itself. A variable declared in a form's module is not available in the public interface. In other words, can not access it or its value from other modules.

You can pass data from one form's class module to another form's class module via a global variable. Global variables can be declared in a general module like this
Public glngMyKeyValue as Long

Another approach is to use Open Args within a docmd statement.

DoCmd.OpenForm "MyFormName", acNormal, , , , acWindowNormal, "This Text needs to be available in the form I am opening."

'Then in the other form's Module, you can access the OpenArgs property

MsgBox Me.OpenArgs

[tmcrouse]

I am running out of time. I am going to fail with this portion and lose my job. So, I have to hire someone or actually pay someone to tell me what I am doing wrong. I know it is probably the simplest solution on the planet but nothing I have tried works. The Getfilter() is in the module. Main module. How to reference QualQ1 to the filter I have no idea. What blows me away with this whole thing is it all works if I have 2 docmds, 1 to open each item and it all updates and does what it should. However, embedding the pivot graph into anything whether report or form, the report or form always come out right and the embedded pivot never updates. So, this tells me the solution is probably so simple it is crazy, but I cannot, no matter how much time I spend find it. Thanks everyone for trying but at this point 60 bucks is well worth it. And, when I get the solution, I will post it so others will not have to pay 60 bucks or spend 3wks spinning their wheels. I do that with all code I figure out where I see tons of posts where people are so frustrated they are ready for nervous breakdowns.

[June7]

I made a quick test of my option 2 - which should work if only one item is allowed to be selected from each listbox.

I changed QualQ1 to add dynamic parameters using LIKE and wildcard under 3 fields:

Like [Forms]![Search_Quality_Programs]![year] & "*"
Like [Forms]![Search_Quality_Programs]![month] & "*"
Like [Forms]![Search_Quality_Programs]![busunit] & "*"

The filter works. Even the pivot chart on report is filtered.

If you want to allow multiple selections from each listbox, then I think will have to use option 3 - modify QualQ1 using VBA and QueryDefs collection.

If you do find a developer to tackle this, be sure to share this thread with them. Good Luck.