Q:docmd.openquery with parameter

[Mark]

All,

For some reason I thought this would be simple, but I can't figure it out.

The line
DoCmd.OpenQuery ("MyQuery")

will open up the query named MyQuery. How do you open up the query if it has
parameters? I tried looking for answers, but everything I see shows how to
put it into a querydef object, but not how to display the query.

Any help appreciated, and thanks!

 

[Roger Converse]

You can paste the query in your VB and assign the variables within the VB SQL.

strsql = "query here"

DoCmd.OpenQuery (strsql)

If you want you can post the sql of your query and I can help with the
variables.

HTH
Roger

 

[Mark]

Hi Roger,

Thanks for the response. I tried that, but access doesn't seem to allow for
SQL strings as a parameter. I created a simple table called "TABLE1".

I tried docmd.openquery("select * from table1"), but it doesn't work. Looks
like it will only take a query name.

I tried docmd.runquery("select * from table1"), which doesn't work becuase
that method requires an action query SQL statement.

 

[david]

Those queries can reference form fields.
So you can put parameters on a form, then OpenQuery.

(david)

 

[Mark]

Hi David,

Thanks for the response. Since this is an internal use database, basically
only being used by me, I went for quick and dirty and just have a module that
I run. I didn't want to create a form just for holding data.

I did something similar and created a table to hold the values. Still, it
would seem that if you can pass parameters to a querydef object that you can
do the same to display the query. But maybe that's not the case.

Again, I really appreciate your help.
-Mark

 

[david]

When you use OpenQuery, parameter requests are passed to
Access instead of to VBA. Access can fullfill the parameter
requests from an Access object - a form. If you are using a
macro, you can use a macro command to set a value to an
Access object before opening the query. Unfullfilled parameter
requests then come up as dialog boxes, so there are then
no unfullfilled requests remaining to pass further up to VBA
when you call the macro action from VBA.

(david)