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 · 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

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

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

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

User · Answer

You can also use TempVars - note     syntax is essential

[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