Code to pass report name and recordsource dynamically

I have a string variable (strReport) for storing a report name and a string variable (strSQL) which I use to build up a sql statement. I want to pass strSQL as the source for the report strReport and open the report for preview.

I have this code that does it when in the development environment:

    DoCmd.OpenReport strReport, acViewDesign
    Reports(0).RecordSource = strSQL
    DoCmd.OpenReport strReport, acViewPreview

but obviously the first line crashes when running the mde.
What code can i use to achieve this?
Who is Participating?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Another approach:

From where ever you are open the report (a command button?) - you could use the OpenArgs argument:

DoCmd.OpenReport "YourReportName", acViewPreview, , , , strSQL   ' strSQL is the OpenArgs parameter to pass to the report.

then in the report On Open event

Private Sub Report_Open (Cancel As Integer)
     Me.Recordsource = Me.OpenArgs
End Sub


Jai STech ArchCommented:
but obviously the first line crashes when running the mde
- wht is the error message ?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
try this:

    DoCmd.OpenReport strReport, acViewPreview
    Reports.YourReportName.Recordsource = strSQL

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:

It's an mde ... you cannot open an object in design view in an MDE.

As has been said, you cannot open a report in design view when working within an MDE.

An alternative is to base your report on a query. Then it is a simple matter to open the query (as a querydef object) in the MDE, change the SQL property of the querydef object and then close the querydef. Then when you open the report in preview mode, the query it is based on has the correct SQL and all should be well...

Dim qd as QueryDef

set qd = Currentdb.Querydefs("NameOfQuery")
qd.SQL = strYourSQLString
set qd = Nothing

then open the report for preview.
normally you would just want to filter the report to the relevant records so you would not need to rebuild the sql unless you are getting the reports records from a different table or sets of table altogether
if its the former, then you just need to use the "WHERE" argument of the docmd.openreport method as shown below. if you need to display numerous "unrelated" records from the underlying query you can insert whatever unique ID field into a temp table and reference that in your "where" clause.
below eg, replace names accordingly

    Dim sWhere As String    
    sWhere = "myFieldID = 100" 'whatever record, or get from a form control etc
    'or get restricted records from a table etc as below
    sWhere = "myFieldID in (select myFieldID from mytempTable)"
    DoCmd.OpenReport "myReport", acViewPreview, , sWhere  
tawandatAuthor Commented:
Thanks DatabaseMX!
That was a simple way of handling it. The only hassle about it would be having the me.recordsource=me.openargs statement on all reports, but it still works fine.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You are welcome.

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.