Form Provide Subreport Criteria?

[misscrf]

I have the following code that I use often on forms which are used to provide report criteria:

For this one issue, I am dealing with an Events report. The form is intended to allow a user to choose 1 event from a drop down and view attendees based on RSVP. The user should be able to choose from a multiselect listbox of RSVP options. They can choose to just see Yes and Maybe, No and No Response, just Yes, etc. Whatever helps them get the event together.

This type of setup works great when all the criteria fields are in the main report control source...
(here is the code)

Private Sub cmdRunRSVPRpt_Click()
On Error GoTo Err_cmdRunRSVPRpt_Click
    Dim strCriteria As String
    Dim lbo As ListBox
    Dim itm
    Dim i As Long
    Dim stDocName As String
            
            
            
            strCriteria = "1=1 "
            strCriteria = strCriteria & _
            " AND ([PKEventID]) = " & Me.cboChooseEvent
        
'        If Me.lstRSVP.ItemsSelected.Count = 0 Then
'            strCriteria = strCriteria _
'        & " AND 1=1"
'        Else
'            strCriteria = "1=1 "
'        strCriteria = strCriteria & _
'        BuildIn(Me.lstRSVP, "[rptEventSubRSVP].Report![MyRSVP]", "'")
'        End If
'
        stDocName = "rptEvent" ' check setting the recordsource for the report
             
        DoCmd.OpenReport stDocName, acPreview, , strCriteria
    
Exit_cmdRunRSVPRpt_Click:
    Exit Sub

Err_cmdRunRSVPRpt_Click:
        
Resume Exit_cmdRunRSVPRpt_Click
   
End Sub

The commented out section is the problem I am having right now. That buildin works by the following function:

Function BuildIn(lboListBox As ListBox, _
        strFieldName As String, strDelim As String) As String
    'send in a list box control object
    'strFieldName is the name of the field in the report's record source
    'strDelim is the delimiter for numbers use "" for text """" and dates "#"
    Dim strIn As String
    Dim varItem As Variant
        
    If lboListBox.ItemsSelected.Count > 0 Then
        strIn = " AND " & strFieldName & " In ("
        For Each varItem In lboListBox.ItemsSelected
            strIn = strIn & strDelim & lboListBox.ItemData(varItem) & strDelim & ", "
        Next
        'remove the last ", " and add the ")"
        strIn = Left(strIn, Len(strIn) - 2) & ") "
    End If
    BuildIn = strIn
End Function

My issue here is that the stDocName = "rptEvent" has a query control source. It also has a subreport (rptEventSubRSVP) which has its own query control source.

The commented section:

If Me.lstRSVP.ItemsSelected.Count = 0 Then
'            strCriteria = strCriteria _
'        & " AND 1=1"
'        Else
'            strCriteria = "1=1 "
'        strCriteria = strCriteria & _
'        BuildIn(Me.lstRSVP, "[rptEventSubRSVP].Report![MyRSVP]", "'")
'        End If
'

is calling a multiselect listbox on the form to allow the user to select no options (i.e. bring back all RSVP choices for an event) or the buildIn (Yes OR No OR Maybe) of the RSVP options the user wants to view for an event.

The issue is that the MYRSVP field called here is on the subreport and in the control source for the subreport query. The above intial code needs to use this build in for the subreport query criteria, but I am not sure how to do that.

Can anyone help me make sense of this minor craziness?

Thanks!



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I use the BuildIn() code to modify the SQL property of the record source of the subreport. For instance if the subreport is based on qselMySubQuery, I would replace the SQL property of qselMySubQuery with a SQL statement created using the BuildIn() function.

Once qselMySubQuery has a new SQL property you can then open the main report.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

I think I get you dhookom, but I don't think I set it up right.

I revised my code to this:

Private Sub cmdRunRSVPRpt_Click()
On Error GoTo Err_cmdRunRSVPRpt_Click
    Dim strCriteria As String
    Dim lbo As ListBox
    Dim itm
    Dim i As Long
    Dim stDocName As String
    Dim strSubSQL As String
    Dim strCriteria2 As String
            
            
            
            strCriteria = "1=1 "
            strCriteria = strCriteria & _
            " AND ([PKEventID]) = " & Me.cboChooseEvent
        
        If Me.lstRSVP.ItemsSelected.Count = 0 Then
            strCriteria2 = strCriteria2 _
        & " AND 1=1"
        Else
            strCriteria2 = "1=1 "
        strCriteria2 = strCriteria2 & _
        BuildIn(Me.lstRSVP, "[MyRSVP]", "'")
        End If

        
        strSubSQL = "SELECT  * FROM  qryRPTEventSubInvite  Where " & strCriteria2
        
        stDocName = "rptEvent" ' check setting the recordsource for the report

        Reports![rptEvent]![rptEventSubRSVP].Report.SourceObject = "QUERY." & strSubSQL
        
        DoCmd.OpenReport stDocName, acPreview, , strCriteria
       
       
    
Exit_cmdRunRSVPRpt_Click:
    Exit Sub

Err_cmdRunRSVPRpt_Click:
        
Resume Exit_cmdRunRSVPRpt_Click
   
End Sub

the important section is this:

Dim strSubSQL As String
Dim strCriteria2 As String

If Me.lstRSVP.ItemsSelected.Count = 0 Then
            strCriteria2 = strCriteria2 _
        & " AND 1=1"
        Else
            strCriteria2 = "1=1 "
        strCriteria2 = strCriteria2 & _
        BuildIn(Me.lstRSVP, "[MyRSVP]", "'")
        End If

strSubSQL = "SELECT  * FROM  qryRPTEventSubInvite  Where " & strCriteria2
        
Reports![rptEvent]![rptEventSubRSVP].Report.SourceObject = "QUERY." & strSubSQL

The thing is that when I choose some options and click the command button to preview the report, nothing happens. I tried to put a stop or break point at the point of the docmd.openreport and it stops there only if I commend out the part where I set the subreport source, and every variable seems to have the right information when I hover over it.

Then I tried to see if I need to make the strSubSQL OpenArgs and pass that to the subreport.

So in the Open of the subreport, I put this:

Private Sub Report_Open(Cancel As Integer)
Static Initialized As Boolean
If Not Initialized Then
Me.RecordSource = OpenArgs
Initialized = True
End If

End Sub

[code]

and added the open args to the docmd:

[code]
DoCmd.OpenReport stDocName, acPreview, , strCriteria, acWindowNormal, strSubSQL

nada. Do you see what I am missing please?

Thanks!




misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I would expect to see code like:

Currentdb.QueryDefs("qryRPTEventSubInvite").SQL = strSQLStatement
DoCmd.OpenReport stDocName, acPreview, , strCriteria, acWindowNormal

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Thank you, Dhookom. I tried to change this around and now I have this:

Private Sub cmdRunRSVPRpt_Click()
On Error GoTo Err_cmdRunRSVPRpt_Click
    Dim strCriteria As String
    Dim lbo As ListBox
    Dim itm
    Dim i As Long
    Dim stDocName As String
    Dim strSubSQL As String
    Dim strCriteria2 As String
            
            
            
            strCriteria = "1=1 "
            strCriteria = strCriteria & _
            " AND ([PKEventID]) = " & Me.cboChooseEvent
        
        If Me.lstRSVP.ItemsSelected.Count > 0 Then
             strCriteria2 = "1=1 "
        strCriteria2 = strCriteria2 & _
        BuildIn(Me.lstRSVP, "[MyRSVP]", "'")
        Else
        strCriteria2 = "1=1 "
        End If

        stDocName = "rptEvent" ' check setting the recordsource for the report
        
        strSubSQL = "SELECT  * FROM  qryRPTEventSubInvite  Where " & strCriteria2
        
        CurrentDb.QueryDefs("qryRPTEventSubInvite").SQL = strSubSQL
        
        DoCmd.OpenReport stDocName, acPreview, , strCriteria, acWindowNormal
       
       
    
Exit_cmdRunRSVPRpt_Click:
    Exit Sub

Err_cmdRunRSVPRpt_Click:
        
Resume Exit_cmdRunRSVPRpt_Click
   
End Sub

The main report opens but whether I choose 1, multiple or no RSVP options from the multiselect listbox, the subreport does not show on the main report.

I put a break point on the docmd and I see that the strcriteria2 shows what it should. I keep trying to put the
"SELECT * FROM qryRPTEventSubInvite Where " & strCriteria2

into the immediate window with a debug.print but nothing happens. I am so close but so far away from finishing this.

This is the last piece of this small little app and then it's done. Any thoughts as to what I am doing wrong? Thanks again!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I would not use following since the query calls itself in a circle.

  strSubSQL = "SELECT  * FROM  qryRPTEventSubInvite  Where " & strCriteria2
  CurrentDb.QueryDefs("qryRPTEventSubInvite").SQL = strSubSQL

Consider creating a new "base" query like "qryRptEventSubInviteBase" that has no filtering. Then your code would be:

  strSubSQL = "SELECT  * FROM  qryRPTEventSubInviteBase  Where " & strCriteria2
  CurrentDb.QueryDefs("qryRPTEventSubInvite").SQL = strSubSQL

Your subreport record source is qryRPTEventSubInvite. You can troubleshoot this by opening the query in the database window.


Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Yeah, I saw that. My qryRPTEventSubInvite is now bunk because it saved it self as

Select * from qryRPTEventSubInvite where "1=1 AND RSVP = 'Yes'"

or something like that.

I went a different route and am trying to make all subreports and queries into 1 big query and then 1 big report with grouping. I tried to do this before and could not get the report to group right.

I may go back to your suggestion as well if this does not work.

So far I made a copy of the main report, and a copy of that record source query. I added in all tables to that query to build the master query of all info with all appropriate lookups and field concatenations (full names etc)

Next, I will take the main report copy and copy all controls onto the main report in group sections and see what I can do to make this all one control source. Then my strcriteria can pull for the whole darn thing.

Sound better?

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Might be better if it works for you and is maintainable.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

So far so good. When I did this the first time, I had issues trying to group everything. I realized this time I can still use a subreport for the Event Hosts because I don't use any of that as criteria. I can also still use a subreport for the invite on behalf of person, so that I don't screw up the grouping. Now just trying to get page layout so I don't get all frustrated in my ocd of making it look nice.

Then I will try the strcriteria all as one.

Thanks for your help. I will post when I get my final solution working or if I still have issues.

misscrf

It is never too late to become what you could have been ~ George Eliot