Can I DoCmd.OpenQuery on a Parameter Query?

[Guest]

Greeting! Thanks for taking the time to look at this!

I've done very little with VBA so I might be trying to do something that
can't be done. Here's what I'm trying to do:

I have a Parameter Query named "Q-Temp" which uses the criteria of
"Like [Please type Vehicle Type]"
So when you run the query from the database view, you get the Enter
Parameter dialog asking you to key-in the vehicle type. This works just great
this way. The field VehicleType is an Integer if that matters...

I need to run that query from VBA. Is there some code that I can add before
or after my:
DoCmd.OpenQuery "Q-Temp"
so that the parameter is passed without the Enter Parameter dialog opening?

Thanks,
Giddy

 

[fredg]

Greeting! Thanks for taking the time to look at this!

I've done very little with VBA so I might be trying to do something that
can't be done. Here's what I'm trying to do:

I have a Parameter Query named "Q-Temp" which uses the criteria of
"Like [Please type Vehicle Type]"
So when you run the query from the database view, you get the Enter
Parameter dialog asking you to key-in the vehicle type. This works just great
this way. The field VehicleType is an Integer if that matters...

I need to run that query from VBA. Is there some code that I can add before
or after my:
DoCmd.OpenQuery "Q-Temp"
so that the parameter is passed without the Enter Parameter dialog opening?

Thanks,
Giddy

Yes, but not the way you are doing it.
Here is a more efficient method, and since it uses a combo box to
enter the wanted Vehicle type, there is no need to have the user
'remember' the value of each vehicle type (or to spell it correctly).

Make a new unbound form.
Add a combo box that will show the VehicleType field.
Make sure the Combo Box Bound Column is the
same DataType as the VehicleType field in the query.
Add a command button to the form.
Code the button's Click event:

DoCmd.OpenQuery "Q-Temp"
DoCmd.Close acForm, Me.Name
Name this form "ParamForm"

Code the Query VehicleTypeID field criteria line
forms!ParamForm!ComboBoxName

Open the form.
Find the VehicleType in the combo box.
Click the command button.

The query will display just those records selected.
The Form will close.

 

[Guest]

Fred:

Thanks for the reply. I tried that and it does work nicely. However, what I
need to do with the VBA code is to keep the user from entering anything. They
already have a large number of tables, queries, forms and reports. Their
project has grown from a flat file to a fairly complex relational model. My
user doesn't want to pick all of the queries by hand any longer. I'm calling
a series of append, update and select queries through VBA just as they have
done by hand in the past.

All I need to do is keep the ENTER PARAMETER dialog from popping up and
waiting for input and it's done. Is there a way to do this through DoCmd?

Thanks,
Giddy
--
“Be yourself; everyone else is already taken.â€

Greeting! Thanks for taking the time to look at this!

I've done very little with VBA so I might be trying to do something that
can't be done. Here's what I'm trying to do:

I have a Parameter Query named "Q-Temp" which uses the criteria of
"Like [Please type Vehicle Type]"
So when you run the query from the database view, you get the Enter
Parameter dialog asking you to key-in the vehicle type. This works just great
this way. The field VehicleType is an Integer if that matters...

I need to run that query from VBA. Is there some code that I can add before
or after my:
DoCmd.OpenQuery "Q-Temp"
so that the parameter is passed without the Enter Parameter dialog opening?

Thanks,
Giddy

Yes, but not the way you are doing it.
Here is a more efficient method, and since it uses a combo box to
enter the wanted Vehicle type, there is no need to have the user
'remember' the value of each vehicle type (or to spell it correctly).

Make a new unbound form.
Add a combo box that will show the VehicleType field.
Make sure the Combo Box Bound Column is the
same DataType as the VehicleType field in the query.
Add a command button to the form.
Code the button's Click event:

DoCmd.OpenQuery "Q-Temp"
DoCmd.Close acForm, Me.Name
Name this form "ParamForm"

Code the Query VehicleTypeID field criteria line
forms!ParamForm!ComboBoxName

Open the form.
Find the VehicleType in the combo box.
Click the command button.

The query will display just those records selected.
The Form will close.