Change Recordsource based on option group?

[jazminecat]

Hi all - I've tried a few approaches to this, and it seems I"m almost there, but I'm stuck. I have a report, with the recordsource of a query called Subrecipient.

I have a form called RunQuery with an option group that allows the user to check either subrecipient, (first option) or contracts (Second option).

the button the form opens the report.

In the on open event of the form I have the following code, which doesn't seem to be working to change the recordsource of the report:

Private Sub Report_Open(Cancel As Integer)

Select Case Forms!RunQuery!Frame15

Case 1 'subrecipients

Case Is = 0
Me.RecordSource = Queries!SubrecipientReport

Case 2 'contracts
Case Is = 1
Me.RecordSource = Queries!ContractReport
End Select
Debug.Print Me.RecordSource

End Sub

Basically I want to change the query that the report is based on depending on what is selected on that form.
I know I can't change the recordsource of a report that's not open, which is why I put this code in the on open event of the form.

but the debug.print shows me the recordsource always stays the same - it stays as subrecipientreport no matter what.

Hope someone can help,

thanks!!

 

[Knicks]

me.recordsource = "Subrecipient

 

[jazminecat]

Herei s what worked in case anyone else ever needs it. Note that i only changed the query names for clarity, it had nothing to do with making this work. the case syntax did.

Private Sub Report_Open(Cancel As Integer)

Select Case Forms!RunQuery!Frame15

Case 1 'subrecipients
Me.RecordSource = "qrySubrecipient"

Case 2 'contracts
Me.RecordSource = "qryContract"
End Select
Debug.Print Me.RecordSource

End Sub

 

[Knicks]

Thats cool.

I tend to use IF THEN statements a bit more than Case but I suppose they both have their benefits.

Changing the recordsource is the way to go as you can use 1 report for multiple situations and more importantly you only have to edit 1 report in the future instead of multiple copies!

 

[jazminecat]

Yeah, it works just great. I think I had to use Case because of the option group - I am used to using If Then statements, but couldn't figure out how to reference the option group, so this turned out to be the fastest solution. so far....until it blows up, of course.

 

[Knicks]

It should have a name like [frame1] (the outter box of the option group). Then each option should have a value like "1", "2", "3", ect..

It should just be something like
(numeric no quotes)

If [frame1] = 1 then

me.recordsoure = "blahblahblah"

ElseIf [frame1] = 2 then

.......

 

[Knicks]

Oops, should tell you how to reference the form

If forms!runquery!frame15 = 1 then

......

Else

......