Using Parameter query in form

[Roy Goldhammer]

Hello there

I have action query with parameters form the current form.

When i use Currentdb.Execute it sometimes use the parameters form the form
or give me an error: two few parameters

What i nee to do that i can execute the action form that always takes the
parameters from the form?

any help would be useful

 

[Marshall Barton]

I have action query with parameters form the current form.

When i use Currentdb.Execute it sometimes use the parameters form the form
or give me an error: two few parameters

What i nee to do that i can execute the action form that always takes the
parameters from the form?

Depends on how your query is set up. If it's a saved query
with parameters, you can use code to set the parameter
values and then use qdf.Execute to run the query.

If you're constructing the query's SQL statement in code,
then you should concatenate the form values into the SQL
string.

I could be more specific if you'd provide more details about
your code, form, and query.

 

[Roy Goldhammer]

Whell Marshal

The form has some controls what the action query use them as parameter

The parameter looks like: Forms!frmName!ctlName

In some cases it work fine and sometimes it doesn't work

can you help me on it?

 

[Marshall Barton]

The form has some controls what the action query use them as parameter

The parameter looks like: Forms!frmName!ctlName

In some cases it work fine and sometimes it doesn't work

This is how you can execute a saved parameter query that
uses form controls for the parameters:

Dim db As Database
Dim qdf As QueryDef
Dim prm As Parameter
set db = CurrentDb()
Set qdf = db.QueryDefs!nameofquery
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
qdf.Execute
. . .
Set prm = Nothing
Set qdf = Nothing
Set db = Nothing

Most of the time, you should use the dbFailOnError argument
to the Execute method.

If you don't mind the popup message boxes and its being
asynchronous with your code, you could use the RunSQL method
instead. This will resolve the parameters and run the query
in a single step, but it does not provide the fine control
and feedback that Execute does.
--
Marsh
MVP [MS Access]

 

[Bonnie]

Hey Marsh! Hello Roy. I was cruisin' the posts and this
one looked interesting. Is it possible that the cursor is
still in one of the parameter fields on the form? I have
some quyeries that run based on date fields keyed into a
form and if the cursor stays in the last field, I don't
get that parameter filled.

-----Original Message-----

The form has some controls what the action query use them as parameter

The parameter looks like: Forms!frmName!ctlName

In some cases it work fine and sometimes it doesn't work

This is how you can execute a saved parameter query that
uses form controls for the parameters:

Dim db As Database
Dim qdf As QueryDef
Dim prm As Parameter
set db = CurrentDb()
Set qdf = db.QueryDefs!nameofquery
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
qdf.Execute
. . .
Set prm = Nothing
Set qdf = Nothing
Set db = Nothing

Most of the time, you should use the dbFailOnError argument
to the Execute method.

If you don't mind the popup message boxes and its being
asynchronous with your code, you could use the RunSQL method
instead. This will resolve the parameters and run the query
in a single step, but it does not provide the fine control
and feedback that Execute does.
--
Marsh
MVP [MS Access]

parameters form the
form

.