How to Programmatically Setting the Data Source of a Sub-Report

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

Is there a way to use report data source constructs such as Me.RecordSource
or Me.Recordset to set the data source of a subreport (or form for that
matter)? How is the linking between the parent report and child report
accomplished? Is there a programmatic way to set the parent child link?

Any ideas will be greatly appreciated!!

Thanks!

Don
 
Don said:
Is there a way to use report data source constructs such as Me.RecordSource
or Me.Recordset to set the data source of a subreport (or form for that
matter)? How is the linking between the parent report and child report
accomplished? Is there a programmatic way to set the parent child link?
Click to expand...

Sub reports/forms use the Link Child Fields and the Link Master Fields
properties of the sub report/form control. Since they are properties
you can change them programmatically.
 
You can assign the RecordSource of a report in its Open event. However, this
generally does not work well for subreports, because Access has to call the
subreport repeatedly for the different spots on the report where the
subreport will appear.

The simplest alternative is usually to create a form where you enter the
limiting criteria for the report, and refer to the controls on the form in
the query that feeds the subreport. Typically the Criteria in the query
reads something like this:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]
The subreport will then read the values from the form each time it needs
them (assuming the form remains open.)

If that is not powerful enough, another option is to write the SQL property
of the QueryDef that feeds the subreport before opening the report. This
kind of thing:
Dim strSql As String
strSql = "SELECT ...
CurrentDb().QueryDefs("Query1").SQL = strSql
DoCmd.OpenReport "Report1", acViewPreview

Forms are completely different. You can reassign the RecordSource of a
subform quite easily at runtime. The link between main form and subform is
defined by the subreport control's LinkMasterFields and LinkChildFields
properties. If you change the RecordSource, Access is likely to reset these
properties to whatever it thinks is a good idea on the day, so you may need
to clear/set them also:

Dim strSql As String
strSql = "SELECT ...
With Me.[NameOfYourSubformHere]
.Form.RecordSource = strSql
.LinkMasterFields = "Field1"
.LinkChildFields = "Field1:
End With

HTH
 
Back
Top