Setting recordsource for report

[BusMgr]

I have a report that is based on a crosstab query. The report is the same, but depending upon whether the client wants to see domestic or international figures, it is based on two different queries. I am trying to set the recordsource dynamically through an if statement, base on selections from a form.

My code is as follows, but I am not sure where to set the .recordsource.

If Forms![frmPrintReports]![Frame27] = "1" Then

stDocName = "qryQTMonthlySales2" 'Run crosstab query.
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "rptQTMonthlySales2" ' New report name!!!!
'Reports!rptQTMonthlySales2.RecordSource = "qryQTMonthlySales2"
DoCmd.OpenReport stDocName, acPreview

ElseIf Forms![frmPrintReports]![Frame27] = "2" Then

stDocName = "qryQTMonthlySales2_International" 'Run crosstab query.
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "rptQTMonthlySales2" ' New report name!!!!
Reports!rptQTMonthlySales2.RecordSource = "qryQTMonthlySales2_International"
DoCmd.OpenReport stDocName, acPreview

End If

I get an Object Required error.

Any help would be appreciated.

Thanks in advance.

BusMgr

 

[RoyVidar]

Hi!

Try the reports on open event:

[tt]If Forms![frmPrintReports]![Frame27] = "1" Then
me.recordsource = "qryQTMonthlySales2"
else
me.recordsource = "qryQTMonthlySales2_International"
end if[/tt]

That would live this routine in the form:

[tt] stDocName = "rptQTMonthlySales2" ' New report name!!!!
DoCmd.OpenReport stDocName, acPreview[/tt]

Roy-Vidar