DoCmd.OpenReport passinh param to underlying query in MSAccess


  • 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.



    Monday, March 19, 2007 4:54 PM

All replies

  • 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.


    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

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

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

    End Sub

    Monday, March 19, 2007 5:33 PM
  • 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.


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

    Monday, March 19, 2007 5:37 PM
  • 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?




    Monday, March 26, 2007 2:44 PM