Clustering filters for report and subReports

[MickelR]

First of all I am impressed by the activity on this forum and the speed in which questions get different points of view. Thank you to those supporting.

My question involves a report (fax layout). Because of the design of this report, I am unable to use the 'detail' section in the main report. As a workaround I have created two subforms and included those two subforms in the report. The information on the report needs to be filtered based on a listbox. I have created a button to activate printing the report. Before printing I set the reports' filter.

Issue: I have to set the same filter for the 'main' report and its two subreports. I created a function for setting the filter like this:

Public Sub Report_SetReportFilter(strReportName As String, strFilter As String)
    
    Dim rpt As Report
    
    DoCmd.OpenReport strReportName, acViewDesign
    
    Set rpt = Reports(strReportName)
    rpt.Filter = strFilter
    rpt.FilterOn = True
    DoCmd.Save acReport, strReportName
    DoCmd.Close acReport, strReportName
    Set rpt = Nothing

End Sub

This sub is called through the following code:

Call Report_SetReportFilter("subFrm1", strFilter)
Call Report_SetReportFilter("subFrm2", strFilter)
Call Report_SetReportFilter("MainForm", strFilter)
DoCmd.OutputTo acReport, "MainForm", acFormatSNP, "Output.snp"

Although the solution works, I don't find this solution very user friendly. MS Access starts to open screens and closing them again. The Excel wizards might be aware of the application.screenupdating feature. Does Access have something similar? Would anyone know a different approach?

I look forward to read your suggestions.

 

[dhookom]

IMO, the preferred method of setting a filter of a report, is simply using the WHERE CONDITION of DoCmd.OpenReport. Opening any object in design view in a running application seems wrong to me.

You will not be able to set a filter like this for a subreport. Subreports get their filters from:
- the link master/child properties of the subreport control
- entering references to controls on forms in the record source query like:
Between Forms!frmDates!txtStartDate and Forms!frmDates!txtEndDate
- changing the SQL property of a saved query (usually with a little DAO code)
- building temp tables to use a record sources

None of these cause the extra screen annoyances.

Duane
Hook'D on Access
MS Access MVP