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

by

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

More Related Contents:

  1. FileDialog doesn’t work
  2. ms access vba code for update records through form
  3. How to add default signature in Outlook
  4. Combine rows / concatenate rows
  5. Getting Error 3340 Query ‘ ‘ is corrupt while executing queries DoCmd.RunSQL
  6. Too few parameters Expected 1, recordset issue
  7. Show “Open File” Dialog
  8. How do I display a messagebox with unicode characters in VBA?
  9. Determining whether an object is a member of a collection in VBA
  10. Distinction between using .text and .value in VBA Access
  11. MS Access VBA Error: Run time error ’70’ Permission Denied
  12. Automatically generating handling of issues
  13. Is it better to use ADO or DAO in Access 2007?
  14. VBA – Folder Picker – set where to start [duplicate]
  15. Prevent enabling of shift bypass – Access 2010
  16. MS Access: how to compact current database in VBA
  17. How to loop through all controls in a form, including controls in a subform – Access 2007
  18. Query for getting value from another record in same table and filter by difference greater than a gap threshold
  19. Working with Unicode file names in VBA (using Dir, FileSystemObject, etc.)
  20. Determine real cause of ODBC failure (error 3146) with ms-access?
  21. Is there an equivalent to Thread.Sleep() in VBA
  22. Exporting MS Access Forms and Class / Modules Recursively to text files?
  23. How to get logged-in user’s name in Access vba? [duplicate]
  24. Pass array to ParamArray
  25. Pass a parameter into an access report programmatically
  26. finding latest file in a folder and opening it (vba access)
  27. Insert multiple records with a date range in MS Access
  28. MS Access VBA: Sending an email through Outlook [closed]
  29. SendKeys is messing with my NumLock key via VBA code in Access form
  30. Runtime error 287. Sending Emails through Outlook using VBA in Access

Leave a Comment