Report/SubForm

[tmcrouse]

MajP, you help!!!! You helped me so much with the GetFilterFromListBox issue and now I have another issue. I am taking the results of that information and have a button where my end-user can click on it and it takes them to a report with a subform pivot graph in my footer/or header. I prefer the header because it looks nicer. My problem is, my subform pivot graph does not update with my GetFilterFromListBox results. I did docmd and then tried run command when the button is selected and the report portion updates to the GetFilterFromListBox, but the subform pivot does not. I think I need to someone link the child and master but do not have a primary key in the GetFilterFromListBox to do that. Do I need to put a hidden one in both? And if I do that how does that hidden, let's say textbox where the primarykey would link know to use a GetFilterFromListBox function? Is there a way to refer to a function a public function that is in the child and master? I have looked online to see if this is possible but not found anything yet.

 

[MajP]

I am taking the results of that information and have a button where my end-user can click on it and it takes them to a report with a subform pivot graph in my footer/or header.
My problem is, my subform pivot graph does not update with my GetFilterFromListBox results.

so I assume your report is opened with some code like this

  Dim strWhere As String
  strWhere = GetFilterFromListBoxes
  DoCmd.OpenReport "yourReportName", acViewPreview, , strWhere

So that the recordsource of the report is limited by the filter.
It sounds like that is working.


My guess is that the graph is not showing child records but summarizing data for the records in the report. So you probably do not have (or need) a link master, link child relationshp. So the trick is to have the graph use the same filter as the main report. Now it is probably based on the main table. Unfortunately you cannot (AFAIK) provide a simple filter to the graph, because the graph is usually based on a crosstab query.

I think I would do it as follows.

1) Create a query definition. Lets call it qryFilteredReport
3) Build your graph and use the "qryFilteredReport" as part of the recordsource
Maybe something like
"TRANSFORM Count(*) AS [Count] SELECT qryFilteredReport.CONDITION_CATEGORY FROM qryFilteredReport GROUP BY qryFilteredReport.CONDITION_CATEGORY PIVOT qryFilteredReport.MTH;

All I am demonstrating is that you will use "qryFilteredReport" as the recordsource. Your query is likely very different.
4) Base the report on qryFilteredReport as well.
5) Now what has to be done is to modify the actual sql of qryFilteredReport.

The code would be something like this before you call the report

dim qdf as dao.querydef
dim strSql as string
Dim strWhere As String
strWhere = GetFilterFromListBoxes
set qdf = currentdb.querydefs("qryFilterdReport")
strsql = "Select * qualQ1 WHERE " & strWhere & " ORDER BY...."
qdf.sql = strWhere

So what this does is changes the sql of the query that the report and the graph are based.
I used qualQ1 as the soure, but you need to start with a query that has whatever fields and tables you need.

If you want to post again, I can take a look.

 

[tmcrouse]

So like place that in the main form in a function, like

Public Function getdata()
Dim qdf As dao.QueryDef
Dim strSql As String
Dim strWhere As String
strWhere = GetFilterFromListBoxes
Set qdf = CurrentDb.QueryDefs("qryFilteredReport")
strSql = "Select * qualQ1 WHERE " & strWhere & " ORDER BY...."
qdf.sql = strWhere
End Function

Private Sub cmdReport_Click()
getdata
DoCmd.OpenReport "rptSearchQuality", acViewReport, , GetFilterFromListBoxes
End Sub

 

[MajP]

That is the basic idea. Not that it matters, but i would change the name from "getData" to "CreateFilteredQuery", that is more descriptive of what you are doing. You are getting the filter and then modifying a stored query to include the filter.

Also I did not (cannot) test this. So those are general ideas of how I would approach it. Also I usually only make something a function if it returns a value, if it does not then it is a sub. Again, it does not matter, but adds clarity.

 

[tmcrouse]

Think I am doing something wrong. I have:

Option Compare Database
Option Explicit

Public Function CreateFilteredQuery()
Dim qdf As dao.QueryDef
Dim strSql As String
Dim strWhere As String
strWhere = GetFilterFromListBoxes
Set qdf = CurrentDb.QueryDefs("qryFilteredReport")
strSql = "Select * qualQ1 WHERE " & strWhere & " Order by ..."
qdf.sql = strWhere
End Function

Private Sub cmdReport_Click()
CreateFilteredQuery
DoCmd.OpenReport "rptSearchQuality", acViewReport, , GetFilterFromListBoxes
End Sub

When I run this run time error 3129 invalid sql statement expected delete insert procedure select or update. When I debug it goes to qdf.sql = strwhere

 

[MajP]

Like I said, it is an example
strSql = "Select * qualQ1 WHERE " & strWhere & " Order by ..."
That would not make a real sql string. The ... meant to continue writing an order by clause.
If you do not need a special order then try

strSql = "Select * qualQ1 WHERE " & strWhere
debug.print strSql

Post back what gets printed in the immediate window.

 

[tmcrouse]

When I run the below code it takes me to the report with embedded graph and the report is correct, but the graph is only showing line 1 of results.

Public Function CreateFilteredQuery()
Dim qdf As dao.QueryDef
Dim strSql As String
Dim strWhere As String
strWhere = GetFilterFromListBoxes
Set qdf = CurrentDb.QueryDefs("qryFilteredReport")
strSql = "Select * qualQ1 WHERE " & strWhere
Debug.Print strSql
End Function

Private Sub cmdReport_Click()
CreateFilteredQuery
DoCmd.OpenReport "rptSearchQuality", acViewReport, , GetFilterFromListBoxes
End Sub

 

[MajP]

can you post the results (sql string) from the immediate window? Can you post the rowsource of the graph (sql string)?

 

[tmcrouse]

MainForm and when clicking on the button that has the report/embedded graph:

Option Compare Database
Option Explicit


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


Public Function CreateFilteredQuery()
Dim qdf As dao.QueryDef
Dim strSql As String
Dim strWhere As String
strWhere = GetFilterFromListBoxes
Set qdf = CurrentDb.QueryDefs("qryFilteredReport")
strSql = "Select * qualQ1 WHERE " & strWhere
Debug.Print strSql
End Function

Private Sub cmdReport_Click()
CreateFilteredQuery
DoCmd.OpenReport "rptSearchQuality", acViewReport, , CreateFilteredQuery
End Sub

Graph Record Source is qryFilteredReport
Report Record Source is qryFilteredReport and in just opening it I see the filter I asked for from that main form is still in the filter area. When combined, as in graph embedded the filter does not apply, if pulling separately with 2 docmds. They do. There is no child master link in the graph and I played around trying to do that but nothing worked out right. I tried even using all the parameters: lob;prod_nm;st_cd, etc and that did not display the results correctly.

 

[MajP]

Can you post the db? It would probably be a lot easier to see than have you explain? What exactly do you want the graph to show? The rowsource for the graph should likely by some query of the qryFilteredReport. Normally it is some count or sum or a couple fields or a crosstab to show summary data. Like effectiveness per state, or total conversions for communication type. You cannot show every field on a graph, would not make any sense.

 

[tmcrouse]

Yeah I can post it, Do I post it here?

 

[MajP]

like you did previously. Just provide a dropbox, 4shared,... or other file sharing link.

 

[dhookom]

I think

Public Function CreateFilteredQuery()
[indent]Dim qdf As dao.QueryDef
Dim strSql As String
Dim strWhere As String
strWhere = GetFilterFromListBoxes
Set qdf = CurrentDb.QueryDefs("qryFilteredReport")
strSql = "Select * qualQ1 WHERE " & strWhere
[COLOR=#EF2929][highlight #FCE94F][b]qdf.SQL = strSQL[/b][/highlight][/color]
Debug.Print strSql[/indent]
End Function

Duane
Hook'D on Access
MS Access MVP

 

[tmcrouse]

I hope I did this upload right with link to share:

http://www.4shared.com/folder/D64SAY0q/_online.html

 

[MajP]

You have a lot of superfulous code that has to get removed, and you did not do most of the things I listed.

1) You need to make the query "qryFilteredReport". Basically select everything from qualq1. Does not really matter, because
you are going to change it anyways but you need a query with this name. So start with
SELECT * FROM qualQ1
2) in your Search_Quality_Programs you need to have this code

Private Sub cmdReport_Click()
  CreateFilteredQuery
  DoCmd.OpenForm "Search_Quality"
End Sub

Public Sub CreateFilteredQuery()
  Dim qdf As dao.QueryDef
  Dim strSql As String
  Dim strWhere As String
  strWhere = GetFilterFromListBoxes
  Set qdf = CurrentDb.QueryDefs("qryFilteredReport")
  strSql = "Select * From qualQ1 WHERE " & strWhere
  Debug.Print strSql
  qdf.SQL = strSql
End Function

3) in the Quality_graph and Search_quality forms delete all code.
4) in the quaity graph form and the Search_quality form set the recordsource to qryfilteredReport
5) in the Search_quality form remove the master child links from the graph

 

[tmcrouse]

Yeah I have had so many things I was attempting to make this work. I did this, everything you stated above and it works awesomely. Such an easy fix. I was making it so much harder than it was. I guess I need to follow my whole thing of KISS, right????? Thank MajP. You are awesome.