Filtering Subreports

[cbrsix]

Hi,



I have a main report that shows our current class offerings. This report is grouped by the class and then each group has a subreport that shows the current class dates for the respective class. I also have a form that the user can enter a FromDate and ToDate to filter the subreports class dates. I cannot figure out how to get this to open the main report with the filtered subreport.

Main Report: LicensingScheduleWeb
This report contains the following fields
- ID, Class, ClassType
Subreport: LicensingScheduleWebSubreport
This report contains the following fields
- ID, ClassID, ClassStartDate

Private Sub cmdPrint_Click()
On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it working.
    'Purpose:       Filter a report to a date range.
    'Documentation: http://allenbrowne.com/casu-08.html
    'Note:          Filter uses "less than the next day" in case the field has a time component.
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.
    
    'DO set the values in the next 3 lines.
    strReport = "LicensingScheduleWeb"      'Put your report name in these quotes.
    strDateField = "[ClassStartDate]" 'Put your field name in the square brackets in these quotes.
    lngView = acViewPreview     'Use acViewNormal to print instead of preview.
    
    'Build the filter string.
    If IsDate(Me.FromDate) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.FromDate, strcJetDate) & ")"
    End If
    If IsDate(Me.ToDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.ToDate + 1, strcJetDate) & ")"
    End If
    
    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    
    'Open the report.
    'Debug.Print strWhere        'Remove the single quote from the start of this line for debugging purposes.
    DoCmd.OpenReport strReport, lngView, , strWhere
    DoCmd.SelectObject acReport, strReport
    DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, , True
    DoCmd.Close acReport, strReport
    DoCmd.Close acForm, "LicensingSchedules"


Exit_Handler:
    Exit Sub


Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
End Sub

[June7]

The only way I have seen this accomplished is with a dynamic parameterized query as the subreport RecordSource.

I never use dynamic parameterized queries so thankfully I've never had to do a report that required additional filter on subreport.

Might be able to set the subreport's Filter property to reference form controls for input. Then set FilterOnLoad property to yes. Never tried this.