Need help on calling a parameter Query thru code

  • Thread starter Thread starter Mota
  • Start date Start date
M

Mota

Hello;
I have a parameter query named "OpenQRY" having 1 parameter "dIDParam" with
this SQL property:
PARAMETERS [dIDParam] Long;
SELECT Name
FROM DrugsTBL
WHERE (((dID)=[dIDParam]));

And I use this code to open it:
Set DB = DBEngine(0)(0)
Set Qdf = DB.QueryDefs("OpenQry")
Qdf.Parameters!dIDParam= 5
Now when i try to open this query using DoCmd.OpenQuery "OpenQRY" ,it askes
dIDParam,although i set it already in my code.it seems the line
Qdf.Parameters!dIDParam is ignored by Access.Whats wrong here?How can i set
a parameter of a stored query thru code?
Thank you for ur help.
 
Access can figure out what the parameter is when the query is run in the
grid. Then Access will inform the Jet Engine what it is.

However, when the same query is run in code, you must tell the Jet engine
what the
parameter is yourself.

This is the slickest way to do it:

With queries that contain parameters that are all references to
controls on open forms, you can simulate the expression service that Access
provides when the queries are run through the user interface, as follows:

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

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenDynaset)
' or qdf.Execute dbFailOnError
 
I think something is mistyped or forgotten to say.The "prm.Name" reflects
name of parameter and has no relation to its value.when i test it,the error
2482 occures saying that Access can not find the name dIDParam u reffered in
ur expression.
In addition,where is "a control on a form" in this code, you mentioned
there?
Thank u so much for ur help.Waiting for answer.

Joe Fallon said:
Access can figure out what the parameter is when the query is run in the
grid. Then Access will inform the Jet Engine what it is.

However, when the same query is run in code, you must tell the Jet engine
what the
parameter is yourself.

This is the slickest way to do it:

With queries that contain parameters that are all references to
controls on open forms, you can simulate the expression service that Access
provides when the queries are run through the user interface, as follows:

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

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenDynaset)
' or qdf.Execute dbFailOnError

--
Joe Fallon
Access MVP



Mota said:
Hello;
I have a parameter query named "OpenQRY" having 1 parameter "dIDParam" with
this SQL property:
PARAMETERS [dIDParam] Long;
SELECT Name
FROM DrugsTBL
WHERE (((dID)=[dIDParam]));

And I use this code to open it:
Set DB = DBEngine(0)(0)
Set Qdf = DB.QueryDefs("OpenQry")
Qdf.Parameters!dIDParam= 5
Now when i try to open this query using DoCmd.OpenQuery "OpenQRY" ,it askes
dIDParam,although i set it already in my code.it seems the line
Qdf.Parameters!dIDParam is ignored by Access.Whats wrong here?How can i set
a parameter of a stored query thru code?
Thank you for ur help.
Click to expand...
Click to expand...
 
The declaration was missing:
Dim prm As Parameter
=====================================

This assumes you have a parameter in your query like this:
Forms![FormName]![ctlName]

When the form is open the ctl has a value in it.
The Eval function evaluates the parameter and assigns the value in this line
of code:
prm.Value = Eval(prm.Name)

--
Joe Fallon
Access MVP



Mota said:
I think something is mistyped or forgotten to say.The "prm.Name" reflects
name of parameter and has no relation to its value.when i test it,the error
2482 occures saying that Access can not find the name dIDParam u reffered in
ur expression.
In addition,where is "a control on a form" in this code, you mentioned
there?
Thank u so much for ur help.Waiting for answer.

Joe Fallon said:
Access can figure out what the parameter is when the query is run in the
grid. Then Access will inform the Jet Engine what it is.

However, when the same query is run in code, you must tell the Jet engine
what the
parameter is yourself.

This is the slickest way to do it:

With queries that contain parameters that are all references to
controls on open forms, you can simulate the expression service that Access
provides when the queries are run through the user interface, as follows:

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

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenDynaset)
' or qdf.Execute dbFailOnError

--
Joe Fallon
Access MVP



Mota said:
Hello;
I have a parameter query named "OpenQRY" having 1 parameter "dIDParam" with
this SQL property:
PARAMETERS [dIDParam] Long;
SELECT Name
FROM DrugsTBL
WHERE (((dID)=[dIDParam]));

And I use this code to open it:
Set DB = DBEngine(0)(0)
Set Qdf = DB.QueryDefs("OpenQry")
Qdf.Parameters!dIDParam= 5
Now when i try to open this query using DoCmd.OpenQuery "OpenQRY" ,it askes
dIDParam,although i set it already in my code.it seems the line
Qdf.Parameters!dIDParam is ignored by Access.Whats wrong here?How can
Click to expand...
i
set
a parameter of a stored query thru code?
Thank you for ur help.
Click to expand...
Click to expand...
Click to expand...
 
Thank you Joe;
But this actually do not work.I cant figure out how we assign name of a
parameter to its value property,and expect it to work.Eval(prm.Name) returns
"dIDParam" from this Sql:
PARAMETERS [dIDParam] Long;
SELECT Name
FROM DrugsTBL
WHERE (((dID)=[dIDParam]));
So,it cant be a Long data type that query Expect as its parameter.
And where is a Form control name in this code?I think something is missing
in that code.
Can u help me more please?
Thanx.

Joe Fallon said:
The declaration was missing:
Dim prm As Parameter
=====================================

This assumes you have a parameter in your query like this:
Forms![FormName]![ctlName]

When the form is open the ctl has a value in it.
The Eval function evaluates the parameter and assigns the value in this line
of code:
prm.Value = Eval(prm.Name)

--
Joe Fallon
Access MVP



Mota said:
I think something is mistyped or forgotten to say.The "prm.Name" reflects
name of parameter and has no relation to its value.when i test it,the error
2482 occures saying that Access can not find the name dIDParam u
Click to expand...
reffered
in
ur expression.
In addition,where is "a control on a form" in this code, you mentioned
there?
Thank u so much for ur help.Waiting for answer.

Joe Fallon said:
Access can figure out what the parameter is when the query is run in the
grid. Then Access will inform the Jet Engine what it is.

However, when the same query is run in code, you must tell the Jet engine
what the
parameter is yourself.

This is the slickest way to do it:

With queries that contain parameters that are all references to
controls on open forms, you can simulate the expression service that Access
provides when the queries are run through the user interface, as follows:

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

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenDynaset)
' or qdf.Execute dbFailOnError

--
Joe Fallon
Access MVP



Hello;
I have a parameter query named "OpenQRY" having 1 parameter "dIDParam"
with
this SQL property:
PARAMETERS [dIDParam] Long;
SELECT Name
FROM DrugsTBL
WHERE (((dID)=[dIDParam]));

And I use this code to open it:
Set DB = DBEngine(0)(0)
Set Qdf = DB.QueryDefs("OpenQry")
Qdf.Parameters!dIDParam= 5
Now when i try to open this query using DoCmd.OpenQuery "OpenQRY" ,it
askes
dIDParam,although i set it already in my code.it seems the line
Qdf.Parameters!dIDParam is ignored by Access.Whats wrong here?How
Click to expand...
Click to expand...
can
i
Click to expand...
Click to expand...
 
Thank you Joe;
But this actually do not work.I cant figure out how we assign name of a
parameter to its value property,and expect it to work.Eval(prm.Name) returns
"dIDParam" from this Sql:
PARAMETERS [dIDParam] Long;
SELECT Name
FROM DrugsTBL
WHERE (((dID)=[dIDParam]));
So,it cant be a Long data type that query Expect as its parameter.
And where is a Form control name in this code?I think something is missing
in that code.
Can u help me more please?
Thanx.

Joe Fallon said:
The declaration was missing:
Dim prm As Parameter
=====================================

This assumes you have a parameter in your query like this:
Forms![FormName]![ctlName]

When the form is open the ctl has a value in it.
The Eval function evaluates the parameter and assigns the value in this line
of code:
prm.Value = Eval(prm.Name)

--
Joe Fallon
Access MVP



Mota said:
I think something is mistyped or forgotten to say.The "prm.Name" reflects
name of parameter and has no relation to its value.when i test it,the error
2482 occures saying that Access can not find the name dIDParam u
Click to expand...
reffered
in
ur expression.
In addition,where is "a control on a form" in this code, you mentioned
there?
Thank u so much for ur help.Waiting for answer.

Joe Fallon said:
Access can figure out what the parameter is when the query is run in the
grid. Then Access will inform the Jet Engine what it is.

However, when the same query is run in code, you must tell the Jet engine
what the
parameter is yourself.

This is the slickest way to do it:

With queries that contain parameters that are all references to
controls on open forms, you can simulate the expression service that Access
provides when the queries are run through the user interface, as follows:

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

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenDynaset)
' or qdf.Execute dbFailOnError

--
Joe Fallon
Access MVP



Hello;
I have a parameter query named "OpenQRY" having 1 parameter "dIDParam"
with
this SQL property:
PARAMETERS [dIDParam] Long;
SELECT Name
FROM DrugsTBL
WHERE (((dID)=[dIDParam]));

And I use this code to open it:
Set DB = DBEngine(0)(0)
Set Qdf = DB.QueryDefs("OpenQry")
Qdf.Parameters!dIDParam= 5
Now when i try to open this query using DoCmd.OpenQuery "OpenQRY" ,it
askes
dIDParam,although i set it already in my code.it seems the line
Qdf.Parameters!dIDParam is ignored by Access.Whats wrong here?How
Click to expand...
Click to expand...
can
i
Click to expand...
Click to expand...
 
Back
Top