Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 2nd, 2007, 11:07 PM
Registered User
 
Join Date: Mar 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Passing parameters to Access query

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
passing a parameter to an Access query! pankaj_daga Access VBA 6 November 16th, 2007 12:26 PM
passing parameters to Access Report murtazasif Access VBA 2 November 6th, 2007 12:32 PM
Passing Multiple Parameters into Access Query rit01 Classic ASP Databases 1 October 26th, 2005 04:00 PM
Access Forms Open Method of Passing Parameters androman Access VBA 8 September 22nd, 2004 09:04 AM
Passing parameter to Access query eapsokha Classic ASP Databases 2 September 16th, 2004 02:49 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.