Hi all,
I created a function that will open a provided Access query, set parameters using DAO.QueryDef and return a recordset. I pass the name of the query and an array of parameters to the function like so:
Code:
Dim arrParameter(0 To 1) As Variant ' two parameters
arrParameter(0) = strFirstName
arrParameter(1) = strLastName
Set rsMember = fProcessQuery("qryMatchMemberToAdult", arrParameter())
So, my function looks like this:
Code:
Dim db As Database
Dim rsRecordSet As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim i As Integer
Dim msgResult As VbMsgBoxResult
Set db = Application.CurrentDb
Set qdf = db.QueryDefs(strQueryProcedure)
' Remember that the parameters collection starts at 0!
' So five parameters would be 0 to 4.
For i = LBound(arrParameter) To UBound(arrParameter)
qdf.Parameters(i).Value = arrParameter(i)
Debug.Print "Parameter " & i & ": Name = " & qdf.Parameters(i).Name & ": Value = " & qdf.Parameters(i).Value
Next i
Debug.Print "Number of Parameters: " & qdf.Parameters.Count
Debug.Print "----------------------------------------------"
Set rsRecordSet = qdf.OpenRecordset(dbOpenForwardOnly)
Set fProcessQuery = rsRecordSet
Set rsRecordSet = Nothing
Exit Function
The goal was to be able to process any query with a variable number of parameters and return the record set to the calling subroutine.
I originally wrote this post to ask for help, but I just figured it out. :D I thought others might benefit from my code, so I post this as a thank you for all those who helped me in the past.
- Jim R.
Passing parameters to Access query
