SQL In Code help!

[Steve]

Hi,

I am trying to use an SQL statement in code to get a count
from a query. When the code runs, i am getting the error
3061 - Too Few parameters. Expected 1

The Code is;

Dim strQ_Type As String
strQ_Type = Forms!frmWorkflow!cmbQueue_List

Get_Counts:

Set db = CurrentDb
Set tmpRst = db.OpenRecordset("SELECT Filter_Name,
Filter_Query FROM tblQueue_Filters WHERE Queue_Type
Like '*" & strQ_Type & "*' ORDER BY Filter_Sort_Order;")

Do Until tmpRst.EOF

' Now to get a count of MPR's within each Queue.
Set rstSQL = db.OpenRecordset("SELECT Count(MPR)
AS CountofMPR FROM " & tmpRst("Filter_Query") & ";")


Next_Query:

tmpRst.MoveNext

Loop

Exit Sub

The error turns up on the set rstSQL line. The query in
the record tmpRst("Filter_Query") does have a parameter
value in it (it relates to a list box on a form), but I do
not know how to overcome the problem.

Can anyone help?

Many thanks in anticipation,
Steve.

 

[Alex Dybenko]

if query have a parameter - then you have to use querydef and assign value
to parameter:

dim qryd as querydef
set qryd=db.querydefs(tmpRst("Filter_Query"))
qryd.parameters("MyParameter")=<whatever>

Set rstSQL = qryd.openrecordset

 

[Steve]

Alex,

I did see an earlier thread with this info in. But I was
trying to write an SQL statement, with the query in to get
a count of records within the query......

As your reply was a bit plainer than the one I was
reading, it suddenly dawned on me, all I need to do is
rstSQL.MoveLast and get a recordcount!

Thanks for that Ray of light )

Happy New Year!
Steve