none
DoCmd.OpenReport passinh param to underlying query in MSAccess

    Question

  • 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

    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.

    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

    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.

    E.g.:

    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?

    bazz

     

     

    Monday, March 26, 2007 2:44 PM