acki4711


Hi all,

- I have in Access (2003) a report who's datasource is a saved query.
- This query needs a param

How can pass the param to the query using the DoCmd.OpenReport method

Any help is highly appreciated.

TIA

Dan




Re: DoCmd.OpenReport passinh param to underlying query in MSAccess

Luiz Claudio - MVP


Hi Dan,

you cannot pass the parameter by using DoCmd.OpenReport method. It would be possible if your parameter referenced a valid location (e.g.: Forms!YourForm!YourTextbox), but it does not seem to be the case.

One way to workaround it is to work with two queries: one is the query you already have (with parameters) - qryBase -, and the second one is the query your report will be based on - qryReport.

The technique consists on passing qryBase's parameters as values to qryReport, and the opening the report.

E.g.:

Sub teste()

Dim db As DAO.Database
Dim qdfBase As DAO.QueryDef
Dim qdfReport As DAO.QueryDef
Dim strSQL As String

On Error GoTo ErrHandler

Set db = CurrentDb()

'Retrieves base query SQL
Set qdfBase = db.QueryDefs("qryBase")
strSQL = qdfBase.SQL

'Replaces parameter's names by values
strSQL = Replace(strSQL, qdfBase.Parameters(0).Name, 123, , , vbTextCompare) 'numeric parameter
strSQL = Replace(strSQL, qdfBase.Parameters(1).Name, "'my value 2'", , , vbTextCompare) 'text parameter

'Sets qryReport SQL
Set qdfReport = db.QueryDefs("qryReport")
qdfReport.SQL = strSQL

ExitHere:
Set qdfBase = Nothing
Set qdfReport = Nothing
Set db = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description & vbCrLf & Err.Number & vbCrLf & Err.Source, vbCritical, "teste"
Resume ExitHere

End Sub







Re: DoCmd.OpenReport passinh param to underlying query in MSAccess

Luiz Claudio - MVP

There is also another way: you can base your report on a non-parameterized query, and then pass the parameters as a WhereCondition argument in OpenReport method.

E.g.:

DoCmd.OpenReport "YourReport", acViewPreview, , "NumericField=123 AND TextField='my value'"







Re: DoCmd.OpenReport passinh param to underlying query in MSAccess

Bazz07

Hi,

If you were to use this approach - how would you then open the report - a report whose fields were bound to a query, the same query you used as your base query(the one with parameters). Not sure how this would work

bazz