Is it possible to pass parameters programmatically in a Microsoft Access update query

Question

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

User · Accepted Answer

I just tested this and it works in Access 2010.

Say you have a SELECT query with parameters:

PARAMETERS startID Long, endID Long;
SELECT Members.*
FROM Members
WHERE (((Members.memberID) Between [startID] And [endID]));

You run that query interactively and it prompts you for [startID] and [endID]. That works, so you save that query as [MemberSubset].

Now you create an UPDATE query based on that query:

UPDATE Members SET Members.age = [age]+1
WHERE (((Members.memberID) In (SELECT memberID FROM [MemberSubset])));

You run that query interactively and again you are prompted for [startID] and [endID] and it works well, so you save it as [MemberSubsetUpdate].

You can run [MemberSubsetUpdate] from VBA code by specifying [startID] and [endID] values as parameters to [MemberSubsetUpdate], even though they are actually parameters of [MemberSubset]. Those parameter values "trickle down" to where they are needed, and the query does work without human intervention:

Sub paramTest()
    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.QueryDefs("MemberSubsetUpdate")
    qdf!startID = 1  ' specify
    qdf!endID = 2    '     parameters
    qdf.Execute
    Set qdf = Nothing
End Sub

User · Answer

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

User · Answer

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  lt  gt  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

User · Answer

You can also use TempVars - note     syntax is essential

User · Answer

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

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

Examples related to vba

Examples related to ms-access

Examples related to parameters

Examples related to ms-access-2010