Setting the RecordSource of a subform

  • Thread starter Thread starter Andrew Backer
  • Start date Start date
A

Andrew Backer

I have a report with a subreport, and I need to be able to set the
RecordSource of the sub report from the main report.

So far I can't seem to find any point in the main form's lifecycle where
I can access Me.SubFormName.Report.RecordSource.

The basic problem is I have a pretty complex query with 3 parameters
that is the source of the subreport. Since I don't know how to pass
parameters to a subreport (2 date times & 1 Int), I just want to set the
recordsource.

How should I go about doing this. I can reach up from the subreport
itself to grab the parent's opeargs, but I really don't want to parse
them again!

This one has me stumped, at least for an elegant solution. Thanks for
any help.
 
Generally it is not practical to set the subreport's RecordSource from any
event in the main report. Here are three alternative approaches:

1. If the main report already has the 3 parameters, you can place text boxes
on the main report for them, and nominate them in the LinkMasterFields of
the subreport control, with the matching fields in LinkChildFields.

2. If the two date/time fields represent a range, that won't work. Another
alternative is to provide a form where the user can enter the parameter
values. Then in the query that feeds the subreport, you can enter criteria
such as:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]
The subreport then reads the values from the form each time it needs them.

3. If none of that works, you can actually create the SQL statement on the
fly, and change the SQL property of the QueryDef that fees the subreport
before you open the report:
dbEngine(0)(0).QueryDefs("MySubreportQuery").SQL = "SELECT ...
 
As always Allen, your answers are great.

I am doing a between on the two dates, which I should have stated but
you infered. I don't actually have a form to base it on, so I think
this is what I am going to do.

I am going to create a new query def and stick the source in there (like
you say in #3) as a placeholder. I am also going to try referencing the
parent report for the parameters, but who knows. I still need to be
able to update the base query, use it in several locations, so having
any actualy sql not centralized gives me the willies.

Thanks again,

-[ Andrew Backer / abacker .@. comcast .dot. net ]-
 
Back
Top