How do I pass a filter to a subreport?

[Guest]

I have a main report that contains two subreports in it. On the main form, I
list all top level categories, and I query them from a lookup table. The
subreports, through a lot of joins, present different data about sub
categories. I have a form that I run where it prompts the user to select a
sub-category for the report. The problem that I'm having is that I can't get
the form to pass the where clause to the subreports. I also can't get the
sub categories to list on the main report without negatively impacting the
report results. To simplify, my end goal is to have a report that lists all
categories, and for each category, shows details about a selected
sub-category. Any suggestions??? Thanks in advance!

 

[Allen Browne]

Hi Julie

This is a perennial issue, with several solutions suitable for different
situations.

First choice would always be to use the LinkMasterFields/LinkChildFields
properties of the subreport. If you open the main report in design view,
right-click the subreport control, and choose Properties, these are on the
Data tab. If it currently says they are linked by ClientID, and you want to
link by CategoryID as well, add the extra field to the properties so you
have:
LinkMasterFields: ClientID, CategoryID
LinkChildFields: ClientID, CategoryID

Since you are using a form where the user enters the limiting values,
another suggestion is to have the subreport's query read the values directly
from the form. For example, if your form has text boxes for StartDate and
EndDate, you limit your subreport to that date range by opening its query in
design view, and entering something like this in the Criteria row under the
date field:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]
It is always a good idea to declare these parameters: Choose Parameters on
the Query menu, and in the dialog enter:
[Forms].[Form1].[StartDate] Date/Time
[Forms].[Form1].[EndDate] Date/Time

If nothing else works, you can always re-write the SQL property of the
subreport's query before you OpenReport. This kind of thing:
strSql = "SELECT * FROM Table1 WHERE ...
dbEngine(0)(0).QueryDefs("Query1").SQL = strSql
DoCmd.OpenReport "Report1", acViewPreview