MS Access Query/Recordset with Parameters

This is how to use a query in code with parameters for a Control as well as manipulate it for a Recordset. Note that you need to leave the Controls Row Source blank so that you don't see the Parameter prompts!

    Dim qryDef As DAO.QueryDef
    Set qryDef = CurrentDb.QueryDefs("qryQueryWithParameters")
    qryDef.Parameters("[ParameterA]") = intAID
    qryDef.Parameters("[ParameterB]") = intBID
    Set Me.lstListControl.Recordset = qryDef.OpenRecordset
    Me.lstListControl.Requery

You can declare the Parameters in the Query itself. I did this using square brackets around them and did the same in the query Criteria as well. This way the query does not think you're using literal Text as it will try and put "double quotes" around them if you don't use [square brackets]!

You can also manipulate the same query in a Recordset in order to iterate the row if you need to do that!

    Dim rstQueryWithParameters As DAO.Recordset
    Set rstQueryWithParameters = Me.lstListControl.Recordset
    
    Do Until rstQueryWithParameters.EOF
        ' Write iterative Code!
        rstQueryWithParameters.MoveNext
    Loop

 

Add comment

Loading