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.6K 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.6K 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-20T10:36:00+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)

    This works great. I have an additional question though. Is there any way to pass the query name to the function? I've tried adding an additional argument to the function q as String, and then Set qdf = db.QueryDefs!q, as well as passing the entire db.QueryDefs!qryParameterTest2 as a string, as well as a couple other things, and none work. If it's not possible, I was thinking having a Select Case maybe based off the frm value, or even a passed argument specifying which query I'm wanting and just having a static list. It's not so big that would be an issue, I just wanted to see if there was any other way to do it first.

    0 comments
  2. Anonymous
    2025-05-20T11:22:59+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.

    0 comments
  3. Anonymous
    2025-05-20T11:49:44+00:00

    Thank you very much for your help. This all works great. I've wasted a bit of time trying to figure this out, and then getting help, but I think it'll save me way more than I've wasted ultimately on this project and future ones.

    0 comments