Change a report's recordsource at runtime

Hello Experts!

I have "Report1", and sometimes I want the recordsource of Report1 to be Query "ABC", and sometimes I want the recordsource to be Query "XYZ".

I'm at the code right before I DoCmd.OpenReport, and I need to make this change.  Can someone help me.  I was trying to do this, but it didn't work.

    stDocName = "Report1"
    Application.Reports(stDocName).RecordSource = "ABC"
    DoCmd.OpenReport stDocName, acPreview

Oh yes, I'm using Access 97!
LVL 33
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Try this:

    stDocName = "Report1"
Docmd.OpenReport stDocName ,acViewPreview,,,,"QueryABC"

Then, in the On Open of the Report:

Private Sub Report_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
   Me.RecordSource = Me.OpenArgs
   ' no record source specified
End If

End Sub


You want to use the DoCmd.OpenReport openargs property to supply the recordsource to your report.  And then in the report itself you would add the code

Sub report_open(Cancel as Integer)

Me.recordsource = me.openargs

End Sub
raterusAuthor Commented:
I saw the OpenArgs solution while searching, but I'm using Access 97 and it isn't available.  Can I just keep track of the "Args" using a global variable, that's what I'm trying now.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

either passing the control source using Open args or

Create a global var in a standard module

    Global gstrRecordSource as string

Set the variable to the query or the SQL you want to

    gstrRecordSource = "myQuery"               or "Select...whatever...  however"

and in the on open of the report...

If Len(gstrRecordSource) then
    Me.recordSource = gstrRecordSource
    MsgBox...    someone messed up!
End if


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"Can I just keep track of the "Args" using a global variable, that's what I'm trying now."

Sure.  Yep ... I think OpenArgs for Reports was not added until A2000!

raterusAuthor Commented:
Great help guys, I got it working exactly the way I needed it.
Glad to assist
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.