Two queries one report (1 Viewer)

tihmir

Registered User.
Local time
Today, 10:19
Joined
May 1, 2018
Messages
257
Filtering two queries one report

Hi all, I need some help, please!
I have report (rpt_Inspections) with two subreports (subrpt_AnotherTask and subrpt_Inspections), which are from queries. I have filter form, with date from and date to and cbo.What should I change in this code to filter both subforms with the filter form?


Code:
Private Sub cmd_Report_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: [URL]http://allenbrowne.com/casu-08.html[/URL]
    '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 = "rpt_Inspections"      'Put your report name in these quotes.
    strDateField = "[DateOfInspection]" 'Put your field name in the square brackets in these quotes.
    lngView = acViewReport     'Use acViewNormal to print instead of preview.
    
    'Build the filter string.
    If IsDate(Me.txt_DateFrom) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txt_DateFrom, strcJetDate) & ")"
    End If
    If IsDate(Me.txt_DateTo) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txt_DateTo + 1, strcJetDate) & ")"
    End If
        
  If Trim(Me.cbo_Worker & "") <> "" Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "[Worker] = '" & Me.cbo_Worker & "'"
  
    End If
    
    ' check if the strWhere has some value
    If Trim(strWhere) = "" Then strWhere = "(1=1)"
        
    '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

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
 

Attachments

  • Database_EN.zip
    234.7 KB · Views: 276
Last edited by a moderator:

June7

AWF VIP
Local time
Today, 09:19
Joined
Mar 9, 2014
Messages
5,423
Options:

1. main report bound to tblInspections and apply filter, subreport Master/Child links synchronize records

3. code behind report sets its own Filter and FilterOn properties

3. dynamic parameterized query as subreport RecordSource

4. button code sets subreport Filter and FilterOn properties, however, strWhere will not work for Tasks because date field has different name, will have to modify code to deal with that
Code:
    DoCmd.OpenReport strReport, lngView, , strWhere
    Reports(strReport).subrpt_Inspections.Report.Filter = strWhere
    Reports(strReport).subrpt_Inspections.Report.FilterOn = True
    Reports(strReport).subrpt_AnotherTask.Report.Filter = strWhere
    Reports(strReport).subrpt_AnotherTask.Report.FilterOn = True
 

tihmir

Registered User.
Local time
Today, 10:19
Joined
May 1, 2018
Messages
257
4. button code sets subreport Filter and FilterOn properties, however, strWhere will not work for Tasks because date field has different name, will have to modify code to deal with that
I changed the fields name (date names) with the same names in both tables as you said! I added the code you wrote for me......

DoCmd.OpenReport strReport, lngView, , strWhere Reports(strReport).subrpt_Inspections.Report.Filter = strWhere Reports(strReport).subrpt_Inspections.Report.FilterOn = True Reports(strReport).subrpt_AnotherTask.Report.Filter = strWhere Reports(strReport).subrpt_AnotherTask.Report.FilterOn = True
.......and everything works exactly as I want!Тhank you very much, June7 !!!
 

Users who are viewing this thread

Top Bottom