Can't set record source for Access report with VBA

I have an unattended Access app generating 3 reports (with DoCmd.OutputTo) with same layout but different source. I tried to define the source in Acivate, Load, Open event. These events are NOT triggered when using OutputTo.

Is that normal ?

Don't want to use openReport with OpenArgs because the app is unattended.

What's the solution ?

bthouinIT Analyst and developerAsked:
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.

Michael FowlerSolutions ConsultantCommented:
The "OutputTo" just outputs your data to a file, it does not open it, so there is no Load or Open or Activate event to trigger these handlers.

One thought here could be to have a global variable with the source which is used by the OutputTo method and so have a loop to run OutputTo after setting this variable correctly for each run
Jeffrey CoachmanMIS LiasonCommented:
If you want a quick and dirty solution, ...just create 3 reports each with its own unique recordsource...

If the report is simple (no complex grouping or vba code, ...etc)
...then you are not saving much by using one report and swamping sources...

Or literally "Open" the one report and load each recordsource, ...then output it.
Do something like this on the open event of the report:
Private Sub Report_Open(Cancel As Integer)
    If Not IsNull(Me.OpenArgs) Then
        Me.RecordSource = Me.OpenArgs
    End If
End Sub

Open in new window

...Then do something like this in your OutputTo code:
Dim strReportName As String
strReportName = "rptYourReport"

DoCmd.OpenReport strReportName, acViewReport, , , acHidden, "SELECT * FROM tblCustomers"
DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, "C:\YourFolder\Report1.PDF"
DoCmd.Close acReport, strReportName

DoCmd.OpenReport strReportName, acViewReport, , , acHidden, "SELECT * FROM tblMembers"
DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, "C:\YourFolder\Report2.PDF"
DoCmd.Close acReport, strReportName

Open in new window

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
Jeffrey CoachmanMIS LiasonCommented:
Don't want to use openReport with OpenArgs because the app is unattended. fine for me...
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Just a side note (and no points please), the OnOpen is the only place where you can mess with a reports recordsource.

bthouinIT Analyst and developerAuthor Commented:
Hi Jeffrey

Thanks for your code, it works like a charm. But very small details can make a huge difference: I started with a DoCmd.OpenReport with just the report name as a parameter. That wreaked havoc. It was asking me where to save the report, wanted a printer name, all that before it even went through the Open event... Just adding "acViewReport, , , acHidden" changed the behaviour completely. I don't think I'd found out about that little code difference which makes such ahuge results difference myself.

You get the points.

Jeffrey CoachmanMIS LiasonCommented:
OK,...Glad I could help...
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.