Code to pass report name and recordsource dynamically

Posted on 2007-09-28
Medium Priority
Last Modified: 2013-11-28
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?
Question by:tawandat
LVL 14

Expert Comment

by:Jai S
ID: 19976899
but obviously the first line crashes when running the mde
- wht is the error message ?
LVL 75
ID: 19976903
try this:

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

LVL 75
ID: 19976906

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 75

Accepted Solution

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 total points
ID: 19976921
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


LVL 11

Expert Comment

ID: 19981262
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.
LVL 19

Expert Comment

ID: 19986901
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  

Author Comment

ID: 19987454
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.
LVL 75
ID: 19987534
You are welcome.


Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question