RunSQL without Textbox

[Guest]

I have two queries. First one is a Select Query which I use to get the data
and set the criteria.
Second one is append query which i use to summaries and store the result in
a table.

1st SQL (Qy_Criteria)
SELECT abc, def
FROM Table1
WHERE def = [Criteria1]

2nd SQL
INSERT INTO Tb_MMM_Result ([abc], [dev])
SELECT sum(abc), [Criteria1] as C
FROM Qy_Criteria
GROUP BY [Criteria1]
HAVING [Criteria1] = [Criteria1]


I konw i can combine the above queries into one query, but actually it is
more complicated. so i have to divide them into serveral queries.
I need to run the above queries many times for changing the [criteria1]
I use vb and use docmd.RunSQL

I set STR as string and = 2nd SQL as follow

STR = "INSERT INTO Tb_MMM_Result ([abc], [dev]) " &_
"SELECT sum(abc), [Criteria1] as C " & _
"FROM Qy_Criteria " & _
"GROUP BY [Criteria1] " & _
" HAVING [Criteria1] = '" & Varialbe & "'"

Docmd.RunSQL str

However, a textbox still apperas and ask me to input [Criteria1].

 

[John Vinson]

I have two queries. First one is a Select Query which I use to get the data
and set the criteria.
Second one is append query which i use to summaries and store the result in
a table.
...
However, a textbox still apperas and ask me to input [Criteria1].

Ummm?

Of course. That's precisely what you're asking it to do.

Running the first query is unnecessary; since the second (or
additional) queries reference the first query, you can simply run them
directly.

To avoid getting the prompt, I'd suggest a couple of possible options:

- Rather than a prompt [Criteria1], run the Queries from a Form,
frmCrit, with a textbox txtCriterion; use a criterion of

=[Forms]![frmCrit]![txtCriterion]

All of the queries can then reference the same value.

- Or, use the Querydef method:

Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim strCrit As String
Set db = CurrentDb
<get your criterion into strCrit somehow, from a Form, from an Input
Box, whatever>
Set qd = db.Querydefs("YourQueryName")
qd.Parameters(0) = strCrit
qd.Execute dbFailOnError
Set qd = Nothing

John W. Vinson[MVP]

 

[Guest]

Many Many Thanks.

I have two queries. First one is a Select Query which I use to get the data
and set the criteria.
Second one is append query which i use to summaries and store the result in
a table.
...
However, a textbox still apperas and ask me to input [Criteria1].

Ummm?

Of course. That's precisely what you're asking it to do.

Running the first query is unnecessary; since the second (or
additional) queries reference the first query, you can simply run them
directly.

To avoid getting the prompt, I'd suggest a couple of possible options:

- Rather than a prompt [Criteria1], run the Queries from a Form,
frmCrit, with a textbox txtCriterion; use a criterion of

=[Forms]![frmCrit]![txtCriterion]

All of the queries can then reference the same value.

- Or, use the Querydef method:

Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim strCrit As String
Set db = CurrentDb
<get your criterion into strCrit somehow, from a Form, from an Input
Box, whatever>
Set qd = db.Querydefs("YourQueryName")
qd.Parameters(0) = strCrit
qd.Execute dbFailOnError
Set qd = Nothing

John W. Vinson[MVP]