Changing SourceObject of a Sub Report at Runtime

[egstatus]

Hi everyone,
I've been reading quite a few posts on this topic and I'm not quite clear if there is an easier solution for this than the ones I've read. So many of these post date back for at least 10 years. I hope ms access has changed since.

I have a main report with 5 different sub reports.
One of the subreports subReportPage4 has 5 different types of reports that I can use/print based on the value of few field on the main report.

If the value of the field "PRODUCT" = "Fixed Rate" then I want to change the sourceobject of subReportPage4 and use the report name 'rptFixedRate'; if the value of "PRODUCT" = "Variable Rate" I want to set the sourceObject of subReportPage4 to 'rptVariableRate', etc.

I have added code to the on Report_Open event but that code does not get executed at all.

Is there an simple solution to this or do I still have to add all the subreports to the main report and then just make the appropriate ones visible? Using this approach can make the reports load slow.

Thanks

Egstatus

 

[MajP]

Try moving the code to the detail section on paint event

 

[egstatus]

Hi MajP,
I tried on the Paint Event of the Details section of the report as suggested but it does not execute at run time either.
The code execute when I'm in design mode and use the 'Report View' option (it executes too many times though). It does not trigger if I use the 'Print Preview'. or when I execute it via:
DoCmd.OutputTo acOutputReport, ReportName

I personally thought that Report_Open event would be the ideal place to change the sourceobject of the subreport.

I will keep trying other things.

Thanks

Egstatus.