Share via

Can you use a public function / sub to set a recordset, or query parameters?

Anonymous
2025-05-19T19:12:38+00:00

I have the following code to set the parameters of a saved query, and then use that as the recordset of a form. Is there a way to do this in a public sub/function in a module? Just to avoid putting this block in the Form_Load of every form that needs it?

Dim db As Database

Dim qdf As QueryDef

Dim rs As Recordset

Dim dExamDateStart As Date, dExamDateEnd As Date

Dim aParameterNames(2) As String

aParameterNames(0) = "parExamDateStart"

'

aParameterNames(1) = "parExamDateEnd"

dExamDateStart = #2/1/2025#

dExamDateEnd = #12/31/2025#

Set db = CurrentDb()

Set qdf = db.QueryDefs!qryParameterTest2

qdf.Parameters(aParameterNames(0)) = dExamDateStart

qdf.Parameters(aParameterNames(1)) = dExamDateEnd

Set rs = qdf.OpenRecordset(dbOpenDynaset)

' do your thing

Set Me.Recordset = rs

Microsoft 365 and Office | Access | Other | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Accepted answer
  1. HansV 460.7K Reputation points MVP Volunteer Moderator
    2025-05-20T10:51:36+00:00

    For example:

    Function SetRecordset(frm As Form, queryname As String)
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Set db = CurrentDb
    Set qdf = db.QueryDefs(queryname)
    qdf.Parameters("parExamDateStart") = #2/1/2025#
    qdf.Parameters("parExamDateEnd") = #12/31/2025#
    Set rst = qdf.OpenRecordset(dbOpenDynaset)
    Set frm.Recordset = rst
End Function

    Call like this:

        Call SetRecordset(Me, "qryParameterTest2")

    If you wish, you can also pass the parameter names and values to the function.

    2 people found this answer helpful.
    0 comments
Accepted answer
  1. HansV 460.7K Reputation points MVP Volunteer Moderator
    2025-05-19T19:49:18+00:00

    In a standard module:

    Function SetRecordset(frm As Form)
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Set db = CurrentDb
    Set qdf = db.QueryDefs!qryParameterTest2
    qdf.Parameters("parExamDateStart") = #2/1/2025#
    qdf.Parameters("parExamDateEnd") = #12/31/2025#
    Set rst = qdf.OpenRecordset(dbOpenDynaset)
    Set frm.Recordset = rst
End Function

    In the Form_Load event procedure:

        Call SetRecordset(Me)
    1 person found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-05-20T11:51:51+00:00

    In an application each parameter would normally be a reference to a control in a form, often an unbound dialogue form.  When establishing a querydef object, rather than naming each parameter, the code can then evaluate each member of the querydef object's Parameters collection.  The following code  snippet is an example:

     

    Public Function WordMerge(strQuery As String, _



                          strDataDoc As String, _



                          strMergeFile As String, _



                          Optional blnSuppressBlankLines As Boolean = True)



                       



    ' Merges data from query into Word document



    ' Accepts:  Name of Access query providing data for merge - String.



    '           Path to Word data file created from query - String.



    '           Path to Word document to be used for merge - String.



    '           Optional setting to suppress or show blank lines



    '           if data missing ( Default = True ) - Boolean



   



    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset



    Dim qdf As DAO.QueryDef



    Dim prm As DAO.Parameter



    Dim wApp As Object



    Dim wDoc As Object



    Dim wActiveDoc As Word.Document



   



    Set dbs = CurrentDb



   



    Set qdf = dbs.QueryDefs(strQuery)



   



    For Each prm In qdf.Parameters



        prm = [removed_js]   ' for some reason the original code here has been replaced. It was, without spaces: p r m = E v a l (p r m . N a m e)



    Next prm



   



    Set rst = qdf.OpenRecordset



 



    ' etc………

     

    The above code comes from my Access to Word automation demo file, which you'll find in AccWord.zip in my Dropbox public databases folder at:

     

    https://www.dropbox.com/scl/fo/0scigd3r48hx5xrev2jrf/AB0-GMdTgMAO5O1cGdr3QW0?rlkey=ib6bs6g9jqcrywwzivur3265t&dl=0

     

    I assume that in your example, assigning date literals to parameters in code is only for illustrative purposes.  Doing so in an operational database would be pointless of course, as the date literals can simply be included in the SQL statement, without the need for any parameters.

    That is good to know. And yes, all the dates and other details were either part of my experimenting, or for demonstration purposes.

    0 comments