Query Parameter

T

Tamer

Hi,
From a mouse button event, I want to open a query and
specify a parameter in my code to open that query. What is
the syntax for that? I know I have to use WHERE, but the
exact syntax is skipping me.

Thanks,
Tamer
 
W

Wayne Morgan

If the parameter is already defined in the query, you can specify the
parameter name in code then open the query as a recordset.

Example:
Dim qdf As DAO.QueryDef, prm As DAO.Parameter
Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Set qdf = db.QueryDefs("qryMyQuery")
Set prm = qdf.Parameters!ParameterName
prm = Me.txtMyTextbox
Set rst = qdf.OpenRecordset
Set rst = Nothing
Set prm = Nothing
Set qdf = Nothing
Set db = Nothing

To specify a parameter value for a stored query that you will be opening in
spreadsheet view, it would probably be easiest to point the parameter to a
control (hidden if you wish) on the form and set the value of that control
in your code before you open the query.

Example:
Criteria in the query:
Forms!frmMyForm!txtMyTextbox

Then in the button's click event
Me.txtMyTextbox = 5 'or whatever you want
DoCmd.OpenQuery "qryMyQuery"
 
T

Tamer

I tried the syntax you sent me, but it gave me an error
message where Set prm is.The syntax I used is the
following, please let me know what I'm missing.

Dim qdf As DAO.QueryDef, prm As DAO.Parameter
Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Set qdf = db.QueryDefs("qry_UCRsFinal")
Set prm = qdf.Parameters!ParameterName
Set prm = [Forms]![frm_SupervisorsLogin]!
[txtSupervisorLogin]
Set rst = qdf.OpenRecordset
Set rst = Nothing
Set prm = Nothing
Set qdf = Nothing
Set db = Nothing

DoCmd.OpenQuery "qry_UCRsFinal"
 
W

Wayne Morgan

This won't work with the DoCmd.OpenQuery because you open a "second copy" of
the query, not the one associated with qdf. Also, where I have
"ParameterName", you need to replace that with the string you are using in
the query for the parameter, such as "[Enter the value]".

To use the DoCmd.OpenQuery method, you'll need to use the second option I
sent. Place the value in a textbox on the form and have the query refer to
the textbox as its criteria.

--
Wayne Morgan
Microsoft Access MVP


Tamer said:
I tried the syntax you sent me, but it gave me an error
message where Set prm is.The syntax I used is the
following, please let me know what I'm missing.

Dim qdf As DAO.QueryDef, prm As DAO.Parameter
Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Set qdf = db.QueryDefs("qry_UCRsFinal")
Set prm = qdf.Parameters!ParameterName
Set prm = [Forms]![frm_SupervisorsLogin]!
[txtSupervisorLogin]
Set rst = qdf.OpenRecordset
Set rst = Nothing
Set prm = Nothing
Set qdf = Nothing
Set db = Nothing

DoCmd.OpenQuery "qry_UCRsFinal"


-----Original Message-----
If the parameter is already defined in the query, you can specify the
parameter name in code then open the query as a recordset.

Example:
Dim qdf As DAO.QueryDef, prm As DAO.Parameter
Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Set qdf = db.QueryDefs("qryMyQuery")
Set prm = qdf.Parameters!ParameterName
prm = Me.txtMyTextbox
Set rst = qdf.OpenRecordset
Set rst = Nothing
Set prm = Nothing
Set qdf = Nothing
Set db = Nothing

To specify a parameter value for a stored query that you will be opening in
spreadsheet view, it would probably be easiest to point the parameter to a
control (hidden if you wish) on the form and set the value of that control
in your code before you open the query.

Example:
Criteria in the query:
Forms!frmMyForm!txtMyTextbox

Then in the button's click event
Me.txtMyTextbox = 5 'or whatever you want
DoCmd.OpenQuery "qryMyQuery"

--
Wayne Morgan
Microsoft Access MVP





.
Click to expand...
Click to expand...
 
Top