Where to set the RecordSource property for a sub-report?

[sjh]

Hi,

I have a report that contains two sub-reports. Depending on user's radio button selection, I want to dynamically change the record source queries for the sub-reports.

I entered this code on the sub-report's Open event.

Private Sub Report_Open(Cancel As Integer)
    If Forms![myForms]!OptionFrame = 1 Then
        Me.RecordSource = "qryCost_Region1"
    Else 
        Me.RecordSource = "qryCost_Region2"
    End If
End Sub

But I get this run-time error '2191':
"You can't set the Record Source property in print preview or after printing has started."

Please tell me where I need to set the RecordSource property for the sub-reports.

Thanks for your help!!!
SJH

 

[dhookom]

What you are attempting is nearly impossible (setting the record source of a subreport from an event in the main or sub report).

There may be a solution if we knew why there needs to be two different record sources and what their differences are.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sjh]

Well, it's a long story. Maybe you can help me figure it out. I feel like there should be a simple solution and I am not just getting it.

It's related to the question I asked on this thread:

http://www.tek-tips.com/viewthread.cfm?qid=932831

In my user form, I have two radio buttons - detail and summary.

If detail is clicked, then a report is generated using this query

SELECT * FROM myTalble
WHERE (detail=IIf([Forms]![Generate Reports]![OptionFrame]=1,True,False)))

This returns IDs 2 and 3.


If summary is clicked, then a report is generated using this query

SELECT * FROM myTalble
WHERE (detail=IIf([Forms]![Generate Reports]![OptionFrame]=2,True,False)))

This returns IDs 2 and 4.


I was wondering if I could combine the two statements into one query and have the criteria change dynamically depending on user's selection.
But I couldn't get it to work, so I made two separate queries and hence the two record sources.


myTable
ID detail summary
1 x x
2 o o
3 o x
4 x o