Passing Filter to SubReport

[DDBeards]

I have a report with two subreports, nothing to link parent to child, but
they do have the same fields to be filtered on. The user selects the values
from a form that are passed to the main form on open. However, I can not set
the filter on the subreports at open of the main form or of the subreport.
please help!

DDBeards

 

[Duane Hookom]

Changing the filter or row source of a subreport at run-time is difficult.
Marsh Barton has offered some suggestions in the past. My normal suggestions
are to either set a criteria in the subreport record source like:
Between Forms!frmDates!txtStart and Forms!frmDates!txtEnd

or change the SQL property of a saved query used in the Row Source property
of the subreport prior to opening the main report.

 

[DDBeards]

Duane, I have tried both Marsh's code and have tried to include the code in
the subreport record source as you suggest. However, it appears that when a
report is included as a subreport the timing of when the filter is set
becomes confusing. Putting the filter on form then referencing it works
great when the subreport is opened by itself, yet bombs when it is used as a
subreport. What is the sequence of opening a report and subreport?

DDBeards

 

[Duane Hookom]

I believe the subreports are rendered first. I don't understand what you mean
by "bombs". What is the record source of your subreport?

 

[DDBeards]

All three data sources for my reoprt comes from queries with the same fields
accept one. That field is working, done, or pending jobs. The main report
shows alot of data over several rows. The two subreports are for pending and
completed projects. They list just the basics. When the report is run a
form comes up and ask for up to five different fields to sort on (supervisor,
worker, team, customer, company). All three of the reports have this data,
but the combination of the filter requested will change everytime the report
is run.

 

[Duane Hookom]

"form comes up and ask for up to five different fields to sort on" by this do
you mean "filter on" rather than "sort on"? "Sort" generally refers to the
order of the records in the report.

Also, I never use a solution that opens a form from code in the report. I
always start with the form open and criteria entered prior to opening the
report.

 

[DDBeards]

You are correct on both accounts. Should of been filter rather than sort and
yes the form opens first, collects the filter information and then passes it
to the report via an openreport code. It is this filter info passed to the
main report that is not being passed along to the subreports? That is what I
am trying to solve.

 

[Duane Hookom]

I gave you the two option that I use in my first reply in this thread. Which
ones did you try implement and what were your results?

 

[DDBeards]

The SQL does not work because it did not reset the query to the original
state after running and I used the Forms!TempFilter!TFilter as the filter
line works great when the report is open independently, it fails when the
report is opened as a subreport

 

[Duane Hookom]

I don't think either of my suggestions pertained at all to the filter
property of any report.
Also, why are you concerned about maintaining the SQL of your saved query
when you will dynamically change it every time the report with the subreport
is run? If you need to persist the original query SQL, create a copy of the
original query and use code like:
CurrentDb.QueryDefs("qselRptA").SQL =
CurrentDb.QueryDefs("qselRptAOriginal").SQL