change recordsource of subreport?

[PK]

i need to change the recordsource of a subreport programatically from the
form that calls the report.

I was successfully doing this by opening the sub-form hidden for edit,
changing the record source, and saving it before calling the master report,
but i have to make an MDE file, and this wont work.... help please!

 

[Erez Mor]

hi
create a public string variable (in a module), say "strRecordSource"
in the form that runs the report button_click event, set this variable to
the name of the table or query or sql sentence - before calling the
OpenReport Command
then in the Sub-Report's Open event, write:
me.RecordSource=strRecordSource

and you're done
Erez.

 

[PK]

Erez - thank you very much for responding.

The problem with setting the record source on a sub-form is that it cant be
set once it is open - ie you get an error after the first occurance of the
subform in your report. i did a little more digging, and found the answer
from Marshall Barton(which was very close to your solution) - thanks again
Erez!

For anyone who is searching for this solution, here is an excerpt from the
other post:

changing a subreport's record source, this is a
little tricky because it must be done in the subreport's
Open event procedure. BUT it can only be done once in the
first occurance of the subreport in the main report. To
achieve this you can use code like this:

Static Initialized As Boolean
If Not Initialized Then
Me.RecordSource = "Query1"
Initialized = True
End If

 

[Allen Browne]

Sounds like you've got it, but another option is to re-write the SQL
property of the subreport's query before opening the report:

Private Sub cmdPreview_Click()
Dim strSql As String
strSql = "SELECT ...
Currentdb.QueryDefs("YourSubreportSourceQuery").SQL = strSql
DoCmd.OpenReport "Report1", acViewPreview
End Sub

 

[PK]

Interesting approach - thanks Allen!