record source property cannot be set on subreport

[newgroup]

Hello

Im trying to link two subreports to a main report based on criteria selected
in a form. I can set the main form record source fine, however when I place
code in the onopen event in the subreport I get an error stating I cannot
set this property at this time. Although if I remove the linkchildfields and
linkmasterfields entries the record source onopen event works fine.

Any ideas of a work around as I need the reports to link...... !?!

Thansk in advance.
Kerri

 

[Allen Browne]

Access reloads the subreport each time that it needs to be run for a record
in the main report. That means the Report_Open event of the subreport is not
able to do what you want.

The simplest solution is to change the query that the subreport is based on,
so it reads the values from the form. For example, you might put something
like this in the Criteria row under the date field in your query:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]

If that approach is not flexible enough, another alternative is to re-write
the SQL property of the query that the subreport is based on *before* you
open the main report. This kind of thing:
Dim strSql As String
strSql = "SELECT ...
CurrentDb.QueryDefs("MySubreportQuery").SQL = strSql
DoCmd.OpenReport "MyMainReport", acViewPreview