Open Report with DoCmd.OpenReport (1 Viewer)

[Rod C]

I am attempting to use the “OpenReport Method” to open a report based on a query that I name in the method.

The method syntax is “DoCmd.OpenReport reportname[, view][, filtername][, wherecondition]”.

My code is as follows “DoCmd.OpenReport "rptAllDataFields1", acPreview, "qryAllDataFields2"”. It won’t use the query I name.

The reason I am using this method is that I want to use the same report with several different queries.

What do you think?

 

[jstutz]

I think the problem is that the WHERE condition is not used to specify the name of a query, but rather an actual WHERE clause from a SQL statement.

For example, if you had a table of books and authors, you might come up with the following statement:

DoCmd.OpenReport "rptAllDataFields1", acPreview, "AUTHOR = 'SMITH'"

Or something similar. For this method, the WHERE condition essentially filters down the main dataset specified in the Record Source of the report.

NOW... what you want to do is really easy with forms, but a bit trickier w/ a report due to the way a report builds itself. To change the report Record Source at runtime, you would have to put some code in the OnOpen event of the report to decide what the record source of the report should be. Once you've determined which record source to use, you would then use one of the following two examples to actually change the record source:

Me.RecordSource = "Select * from YourTable where FieldName= 'criteria'"

OR....

me.recordsource = "qryName"

One last little example which also might help you. I pulled this from access help. It shows how to create a new report programmatically. You might get some use out of this code too:

-----------------
Sub NewReport()
Dim rpt As Report

' Return variable of data type Report pointing to
' new Report object.
Set rpt = CreateReport
' Set properties for new report.
With rpt
.RecordSource = "Products"
.Caption = "Products Report"
End With
' Restore new report.
DoCmd.Restore
End Sub
-----------------


HTH
Jamie

 

[Rod C]

Thank you for the help.

Actually the query name goes in the "filtername" argument of the method not the "wherecondition".