How to change the source object of a subreport? (1 Viewer)

Hi,

I have a report, within which is a subreport. I would like to be able to change the source object of that subreport to different forms depending on the status of one of the fields of the parent form record.

Basically this main report prints quote details of doors. I would like to display a different subform with different info for a fire door than I would for a ventilated door for example.

The piece of code I am using is as follows:

If [Door Part Number] Like "B*" Then
Me.frm_BDoorDetails.SourceObject = "rpt_Quote/AckBDoor"
Else
Me.frm_BDoorDetails.SourceObject = "rpt_Quote/AckFirelock"
End If

I am running this code on the OnFormat event of the detail record of the parent report. However, when I run the report I get the following error message:

"You can't set the Source Object property in print preview or after printing has started"

Maybe I am running the code on the wrong event?

If anyone can help me out with this I would be eternally grateful!

Thanks in advance

John

Can you do it in the on open event?

If I try that it says it can't find [Door Part Number] to evaluate

Still can't work out on what event I should perform this action. If anyone can help I would be most grateful!

I can get around the problem by having several subreports on screen and hiding the ones I don't want, but there will probably be about a dozen so this is a bit messy. Also, to get the report to dsiplay correctly in this way would mean placing all supreports on top of one another which isn't great from a development point of view unless someone knows a better way to have several subreports which would be displayed in the same place?