Filtering a Complex Report by Form

[Robert Neville]

I would like to add filter functionality to open several reports
through a form. This question may be rudimentary, yet I have less
experience with filtering data outside from queries.

Here's the scenario my report groups by project names; grouped by
companies; has a subreport for project addresses for multiple
addresses under project grouping; has a subreport for company
addresses for multiple addresses under company grouping; has a
subreport for contacts. The report has a record source to the project
table, tblProj. All subreport have a record source combining the
relationship table with the primary table (trelCompAddr and tblAddr).
A company could have multiple addresses. The relationship table,
trelCompAddress contains phone numbers and email data.

I would like to filter the report by area code meaning that only
projects or companies with a particular area code and phone number
would print. Normally, I would create a query with tblProj,
trelProjAddr, and tblAddr; and set the phone field criteria to Like
212*. But the report has two phone number fields in different tables,
trelProjAddr and trelCompAddr. Second, changing the record source to a
jumbo query with all tables becomes cumbersome and list some projects
or companies more than once. Thus the jumbo query return duplicates
for some reason. This scenario happens when the project or company has
multiple addresses. This feat seems more ambition without changing the
report grouping or removing the three sub-report.

In an ideally world, I could open the report and change the record
source to include the criteria from the form. The report would return
the record source query to the original upon exit. But this approach
becomes a challenge with sub-report events firing before the main
report. Let me know if you could set the record sources for the
sub-reports from a form. How?

Let me know if someone could elaborate more on opening a report and
restricting the dataset under a complex relationship structure.
Other threads mentioned that Access's filter property is unreliable.
Please confirm the reliability of Access's Filter Property and how it
should be used. In addition, please be explicit about your
suggestions. I am having trouble referring to the report and
sub-report from a separate form or module.

 

[Larry Linson]

I'm sorry, but I am having a difficult time following your
description, particularly the part about "changing the Query to a
jumbo Query". I'd think that the table that is the primary source of
data for the main report would NOT be in the RecordSource for the
Reports embedded in the Subreport Controls, but that they would be
synchronized via the LinkMasterFields and LinkChildFields of the
Subreport Controls. Thus, I wouldn't see any problem in creating a
Form allowing the user to choose the particular ZIP and phone prefix,
and using those to create the WhereCondition argument of a
DoCmd.OpenReport or to build a Query with a WHERE clause to replace
the one in the RecordSource of the Report.

Perhaps if you would clarify, someone could help.

Larry Linson
Microsoft Access MVP

 

[Robert Neville]

Larry,

Thanks for your reply. Let know what area needs further clarification.

Essentially, my goal is to create a form that allows me to choose the
criteria for a report. The obvious answer would be use the
DoCmd.OpenReport with the FilterName or the WhereCondition. But my
situation is more complex due to the nature of the report. The report
list all related data for each Project including project specific
fields, project addresses and project phone numbers; it list
Companies, Company addresses, and Company phone numbers; The report
has three subreport (explained in my previous post). My goal is
ambition and involves filtering the report on any number of fields
through a form interface.

The driving table for the report is the Project table, tblProj. The
Project Address subreport's record source has a query combining
trelProjAddr and tblAddr; there by relating to tblProj through the
ProjID field. The Company subreport's record source has a query
combining trelCompProj, tblComp, trelCompAddr, and tblAddr; there by
relating to tblProj through the ProjID field. The Contact subreport's
record source has a query combining trelContProj and tblCont; thereby
relating tblProj through the ProjID field.

My jumbo query reference relates to combining all the tables in one
select statement that would form the basis for the Project report's
record source. Use the following Select statement as an example.

SELECT tblProj.*, trelCompProj.*, trelProjAddr.*, tblComp.*,
trelCompAddr.*, trelContProj.*, tblCont.*, tblAddr.*
FROM (((tblProj LEFT JOIN trelProjAddr ON tblProj.ProjID =
trelProjAddr.ProjID) LEFT JOIN tblAddr AS tblAddr_1 ON
trelProjAddr.AddrID = tblAddr_1.AddrID) LEFT JOIN ((tblComp LEFT JOIN
(tblAddr RIGHT JOIN trelCompAddr ON tblAddr.AddrID =
trelCompAddr.AddrID) ON tblComp.CompID = trelCompAddr.CompID) RIGHT
JOIN trelCompProj ON tblComp.CompID = trelCompProj.CompID) ON
tblProj.ProjID = trelCompProj.ProjID) LEFT JOIN (tblCont RIGHT JOIN
trelContProj ON tblCont.ContID = trelContProj.ContID) ON
tblProj.ProjID = trelContProj.ProjID;

The jumbo query seems to list project more than once.

My objective involves creating complex filter on several fields. I
need to filter between two STATUS dates; and limit the projects to
those with local phone numbers or companies with local numbers. To
achieve this aim, I may need to add criteria to the queries in the
record source for the main report and two subreports.

Again, this situation creates a challenge for me. I am deliberating on
the best approach. One idea involves changing the queries behind the
report and not worrying about opening the report with a filter. But
how to do you remove the criteria so the code could reapply the WHERE
statement the next time around?

The second idea involves changing the main report's record source and
sub-report's record source at run-time to a temporary query. The code
would delete any temporary query before creating a new one.

The third idea involves setting the filter property on the main report
and each subreport.

I still have a fundamental understanding about refer to Access
collection. I always get confused on the syntax. How to proper refer
to a report or subreport from form? Can you change the report's record
source from the form or do you pass the criteria values through the
OpenArgs? Could someone explain the event order while opening a report
on Open_Report? Apparently, Sub-report fire first.

Again, let me know if you have any suggestions. Be as specific as
possible.