Function using predefined query

[pdbowling]

Hi all.
I wrote a query and saved it. It counts records.
When I click on the query I get 3074 as the result.

I wrote a function that calls the query, but the function returns 0. Here is the code. It is a carbon copy of another function that does exactly the same thing and works perfectly (for a different query). Any clues as to why this is giving me the wrong answer?

PB

Function countEReqs(myStore)
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command

With cmd
.ActiveConnection = cnn
.CommandType = adCmdTable
.CommandText = "ReqCount"<==predefined query (confirmed name,right)
End With

Set rst = New ADODB.Recordset

Form_report.Label31.Caption = "Counting Reqs"
Form_report.Repaint
Set rst = cmd.Execute <===here maybe????
Form_report.Label31.Caption = "Finished count"
Form_report.Repaint

myQty = rst.Fields("Qty") <===here maybe????
If IsNull(myQty) Then <==a stop point shows myQty = 0 before if stmnt
myQty = 0 <== I checked and it never enters this if statement
End If

DoCmd.SetWarnings False
Form_report.Label31.Caption = "Updating"
Form_report.Repaint
mySQL = "Update results set eReqs = " & myQty & " where Store = '" & myStore & "' and StartDate = #" & myStartDate & "#"
DoCmd.RunSQL mySQL
Form_report.Label31.Caption = "Finished updating"
Form_report.Repaint

Set cnn = Nothing
Set rst = Nothing
Set cmd = Nothing

countEReqs = myQty
End Function

This is the query text
SELECT Count(GeteReqs.ROWSTAMP) AS Qty
FROM GeteReqs;

 

[ghudson]

Is there a reason that you are not using the DCount function to count your records? Check the help files for the DCount function for more info and options on how to set up your counting criteria.