Query Parameter

  • Thread starter Thread starter Tamer
  • Start date Start date
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
 
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"
 
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"
 
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...
 
Back
Top