Passing parameters to a Select Query in VBA

[Nicolae Fieraru]

Hi All,

I have a table, tblCustomers, with fields SalutationID and Firstname.
I made a query, qrySelect = "Select FirstName from tblCustomers Where
SalutationID = [prmSalutationId]"
If I run this query by itself, it works fine. It asks me for the parameter
and then it shows the records . What I want is to be able to pass the
parameter to it in VBA

I am using the following code:

Dim stDocName as String
Dim db as Database
Dim qdf as QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("qrySelect")
qdf.Parameters["prmSalutationId"] = 1

DoCmd.OpenQuery "qrySelect", acNormal, acEdit

When I run this code, the query is asking for the parameter, as if I didn't
pass it.
I don't want to let the query pick the value of a component from the form

Any help appreciated

Regards,
Nicolae

[Reggie]

Nicolae, the reason it prompts you for the parameter is because you are
opening the query with your last statement. Instead, issue the
openrecordset method on the recordset object. Also, not sure, but think you
need parenthesis instead of brackets. Try this:

Dim stDocName as String
Dim db as Database
Dim rs as Recordset
Dim qdf as QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("qrySelect")

qdf.Parameters("Please enter a Salutations ID") = 1 'or a variable could be
used here

Set rs = qdf.OpenRecordset

Hope this helps.

--
Reggie

----------
"Nicolae Fieraru" <no****@please.cxm> wrote in message
news:41********@duster.adelaide.on.net...

Hi All,

I have a table, tblCustomers, with fields SalutationID and Firstname.
I made a query, qrySelect = "Select FirstName from tblCustomers Where
SalutationID = [prmSalutationId]"
If I run this query by itself, it works fine. It asks me for the parameter
and then it shows the records . What I want is to be able to pass the
parameter to it in VBA

I am using the following code:

Dim stDocName as String
Dim db as Database
Dim qdf as QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("qrySelect")
qdf.Parameters["prmSalutationId"] = 1

DoCmd.OpenQuery "qrySelect", acNormal, acEdit

When I run this code, the query is asking for the parameter, as if I didn't pass it.
I don't want to let the query pick the value of a component from the form

Any help appreciated

Regards,
Nicolae

[Nicolae Fieraru]

Dear Reggie,

Thank you for your help. The brackets were a typo, I wrote the code in the
email and put by mistake brakets.
The qdf.OpenRecordset command works fine, but it doesn't open the query on
the screen. I need to view the results of the query on the screen.

Regards,
Nicolae

"Reggie" <No**********@NoSpamsmittysinet.com> wrote in message
news:Vc********************@comcast.com...

Nicolae, the reason it prompts you for the parameter is because you are
opening the query with your last statement. Instead, issue the
openrecordset method on the recordset object. Also, not sure, but think you need parenthesis instead of brackets. Try this:

Dim stDocName as String
Dim db as Database
Dim rs as Recordset
Dim qdf as QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("qrySelect")

qdf.Parameters("Please enter a Salutations ID") = 1 'or a variable could be used here

Set rs = qdf.OpenRecordset

Hope this helps.

--
Reggie

[Reggie]

One way I know to do it is design 2 procedures in a standard module
something like this (got from
(http://www.mvps.org/access/queries/qry0005.htm)
Option Compare Database
Option Explicit
Public strname As String

Function SetValue(str As String)
strname = str
End Function

Function GetValue()
GetValue = strname
End Function
Now on click of button or whatever, pass a value to set the public variable
in the standard module. In your query on the criteria line of the field you
are filtering for type in =GetValue() and it will read the value you passed
to the SetValue function. Then it will open the query. Change the data
types as appopriate. What I have here opens my query showing me all records
where the fName field = Reggie. Hope this helps

Private Sub Command10_Click()
Dim str As String
str = "Reggie"
Call SetValue(str)
DoCmd.OpenQuery "qry1", , acEdit
End Sub

--
Reggie

----------
"Nicolae Fieraru" <no****@please.cxm> wrote in message
news:41********@duster.adelaide.on.net...

Dear Reggie,

Thank you for your help. The brackets were a typo, I wrote the code in the
email and put by mistake brakets.
The qdf.OpenRecordset command works fine, but it doesn't open the query on
the screen. I need to view the results of the query on the screen.

Regards,
Nicolae

"Reggie" <No**********@NoSpamsmittysinet.com> wrote in message
news:Vc********************@comcast.com...

Nicolae, the reason it prompts you for the parameter is because you are
opening the query with your last statement. Instead, issue the
openrecordset method on the recordset object. Also, not sure, but think you

need parenthesis instead of brackets. Try this:

Dim stDocName as String
Dim db as Database
Dim rs as Recordset
Dim qdf as QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("qrySelect")

qdf.Parameters("Please enter a Salutations ID") = 1 'or a variable

could be

used here

Set rs = qdf.OpenRecordset

Hope this helps.

--
Reggie