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
raterusAsked:
Who is Participating?
 
RgGray3Connect With a Mentor Commented:
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
Else
    MsgBox...    someone messed up!
End if


Me
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
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
Else
   ' no record source specified
End If

End Sub

mx
0
 
matthewrhoadesConnect With a Mentor Commented:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21268869.html

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
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
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.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
"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!

mx
0
 
raterusAuthor Commented:
Great help guys, I got it working exactly the way I needed it.
0
 
RgGray3Commented:
Glad to assist
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.