Changing a Report's RecordSource during runtime...

[Guest_imported]

I have a situation where, on a form, the user selects among various option buttons, the results of which each require a unique query for the underlying Report. I don't really want 9 reports that are the same, only that the RecordSource be assigned to the opening Report...

I've tried "On open" on the report, by having it look to an Open Form form the correct query. Also have tried setting the Report's RecordSource from code in the form, neither of which seem to work. Any ideas? Thanks.

 

[renaudhirsch]

Hi,
Here is a solution:
In the code of your form, put the following code lines to launch the report:

CurrentDb.QueryDefs("MyQuery&quot.Sql = "SELECT ..."
Call DoCmd.OpenReport("MyReport", , "MyQuery&quot

 

[Guest_imported]

Renaud-- thanks for the reply. I have tried all types of combination but get errors.. as you can see, I am trying to set up a Report Query based on User input:

Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

Select Case txtQRpt
Case 1
[txtRpt] = "qyProjSamplesCM"
Case 2
[txtRpt] = "qyProjSamplesCSCMRDL"
Case 3
[txtRpt] = "qyProjSamplesCSRDL"
Case 4
[txtRpt] = "qyProjSamplesCS"
Case 5
[txtRpt] = "qyProjSamplesCMRDL"
Case 6
[txtRpt] = "qyProjSamplesCSCM"
Case 7
[txtRpt] = "qyProjSamplesRDL"
Case 8
[txtRpt] = "qyProjSampRDL"
Case 9
[txtRpt] = "qyProjSamplesCSCM"
End Select

CurrentDb.QueryDefs("qyProjSamplesCSCM&quot.SQL = "SELECT ..."
Call DoCmd.OpenReport("rptSampleResults", acViewPreview, "qyProjSamplesCSCM&quot

Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdPrint_Click