Programmatically Setting Subreport RecordSource

[risicare]

I'm having difficulty figuring out how best to set the recordsource of a subreport using vba.
The key point is that the recordsource of the subreport needs to change in response to a changing value in the detail section of the parent report.
Is there any way that this can be done?
Many thanks
Jason

 

[vbajock]

Look at the form recordsource property in the help file.

Use .requery to update the change.

 

[faeryfyrre]

OK. Is your Parent report designed to report on multiple records or 1 individual record?

This will work if parent report is for 1 individual report but i'm not sure whether it'll work on multiple records.

To change the subreport's recordsource via vba you would use the OnFormat event and test for the changing value and change the subreports recordsource accordingly.



Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[risicare]

The Parent report displays multiple records.
I've got it half working, setting the subreport recordsource using the Report_Open event of the subreport. This will not work if the child and parent properties of the subreport are used.
Unfortunately I cannot figure out how to change the WHERE clause in reponse to a changing value in the Parent report recordset.
As others have reported in Tek-Tips changing the recordsource using the Detail_Format event gives an error.
I suspect this cannot be done. A solution has not been forthcoming in any of the other threads I've looked at.
I'm quite happy with my 'halfway' house, but I do like to get lots of flexibility into my code!
Thanks
Jason

 

[faeryfyrre]

OK,
When you say you want to change the recordsource of the subreport, are you wanting to still display information from the same table (and just change the parameters of what to display) or from different tables?

Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[risicare]

The recordsource of the subreport is a SQL (SELECT, FROM, WHERE) statement. Everything but the WHERE clause remains the same. Therefore, the subreport displays information from the same tables.