• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 691
  • Last Modified:

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!
0
raterus
Asked:
raterus
  • 2
  • 2
  • 2
  • +1
4 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
matthewrhoadesCommented:
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
 
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
RgGray3Commented:
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 MVP, Access and Data Platform)Commented:
"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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now