Using Parameter query in form

  • Thread starter Thread starter Roy Goldhammer
  • Start date Start date
R

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
 
Roy said:
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?
Click to expand...


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.
 
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?
 
Roy said:
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
Click to expand...

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]

 
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-----
Roy said:
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
Click to expand...

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
Click to expand...

.
Click to expand...
 
Back
Top