Passing Fixed Variables to Access Parameter Queries

[Joust Williams]

We have a bunch of queries already made that ask for one parameter. I can do something as basic as use the macro tool in Access to execute said queries. But how do I automate putting in the value (which is fixed) in the dialog box and run the query?

I have to run each query 6 times, one time for each thing I have to look for.

And here is the parameter query execute line in VBScript:

DoCmd.OpenQuery "QUERY", acViewNormal, acEdit

So right now, I have to run query, type in parameter, print output, and so forth 6 times for each query, 16 queries.


Right now this just asks for the parameter, so user interaction is required. Google is not helping with this very basic question as it's almost too basic to even get a hit.

[Josh]

[kingoffighters]

I think you have to write a vb program (within access) to do that, but I have never done it.

[K3V]

Apply frozen corn to it.

[Joust Williams]

Or what about this

Here's what the query is now:
SELECT x.cow, x.[blah blah], x.beh, x.[beh beh], x.meow, x.[boo boo], x.beee, x.[bah bah]
FROM x
WHERE (((x.cow)=[Enter the parameter here]) AND ((x.beee)="stuff"))
ORDER BY x.[bah bah];


Can I write a new query in VB that basically looks like this?

For i = 1 to 6
If i = 1 then NameCompare = "John Doe"
.
.
If i = 6 then NameCompare = "Joan Walker"
QUERYFOO=the above but instead of parameter, it is the variable NameCompare
Exceute Query --> DoCmd.OpenQuery "QUERYFOO", acViewNormal, acEdit
Print Results

Next i

The problem is I don't know the syntax of the assigning of the query statement...first I don't know anything about variables in SQL and second even if I copy a working query into VB it does not compile

[kingoffighters]

I googled this shit:

To pass parameters to a query in VBA is really quite simple:

'First we'll set some variables:

Dim qdf As Querydef
Dim rst As Recordset

'then we'll open up the query:

Set qdf = CurrentDB.OpenQueryDefs(qryName)

'Now we'll assign values to the query using the parameters option:

qdf.Parameters(0) = qryStartDate
qdf.Parameters(1) = qryEndDate

'Now we'll convert the querydef to a recordset and run it

Set rst = qdf.OpenRecordset

'Run some code on the recordset

'Close all objects
rst.Close
qdf.Close
Set rst = Nothing
Set qdf = Nothing

Here is the topic, you can download some examples there if you are still having trouble.

[kingoffighters]

So does it work Joust?

[D_N_G]

joust actually meant he wanted to use his queer-eye to make a pass into a Virile Boy's Anus

[Joust Williams]

I dunno if it works. It should work, but I haven't tried it. Thanks though