[vba] Is it possible to pass parameters programmatically in a Microsoft Access update query?

I have a query that's rather large, joining over a dozen tables, and I want to pull back records based on an id field (e.g.: between nStartID and nEndID).

I created two parameters and tested them as criteria and they work fine.

The issue is, I need to run an insert query from this main query, and need the parameters where they are, in the main query. So, I need to pass parameters to it programmatically.

Anyone have a clue as to how this can be done?

Thanks.

Plenty of responses already, but you can use this:

Sub runQry(qDefName)
    Dim db As DAO.Database, qd As QueryDef, par As Parameter

    Set db = CurrentDb
    Set qd = db.QueryDefs(qDefName)

    On Error Resume Next
    For Each par In qd.Parameters
        Err.Clear
        par.Value = Eval(par.Name)          'try evaluating param
        If Err.Number <> 0 Then             'failed ?
            par.Value = InputBox(par.Name)  'ask for value
        End If
    Next par
    On Error GoTo 0

    qd.Execute dbFailOnError
End Sub

Sub runQry_test()
    runQry "test"  'qryDef name
End Sub

You can also use TempVars - note '!' syntax is essential

Try using the QueryDefs. Create the query with parameters. Then use something like this:

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("Your Query Name")

qdf.Parameters("Parameter 1").Value = "Parameter Value"
qdf.Parameters("Parameter 2").Value = "Parameter Value"
qdf.Execute
qdf.Close

Set qdf = Nothing
Set dbs = Nothing

Many thanks for the information about using the QueryDefs collection! I have been wondering about this for a while.

I did it a different way, without using VBA, by using a table containing the query parameters.

E.g:

SELECT a_table.a_field 
FROM QueryParameters, a_table 
WHERE a_table.a_field BETWEEN QueryParameters.a_field_min 
AND QueryParameters.a_field_max

Where QueryParameters is a table with two fields, a_field_min and a_field_max

It can even be used with GROUP BY, if you include the query parameter fields in the GROUP BY clause, and the FIRST operator on the parameter fields in the HAVING clause.