Returning values from a RecordSet

[Guest]

Why does this not return the correct answer:

Dim rs As DAO.Recordset
Dim strSql As String

'Note: I was trying to do it with a variable on a call but kept getting an
error
'with this:
'**********Commented out ************
' strSql = "SELECT Count(*) As vCount, " & _
' "FROM [" & TableName & "] " & _
' "WHERE " & WhereClause

'Since I could not make that work I decided to hard code in the SQL of a
query as:

strSql = "SELECT Count(Batch05.Co) as vCount " & _
"FROM Batch05 INNER JOIN ChartOfAccounts ON (Batch05.Account =
ChartOfAccounts.Account) AND (Batch05.Co = ChartOfAccounts.Co) " & _
"WHERE (((ChartOfAccounts.Special)='AR'));"


Set rs = CurrentDb.OpenRecordset(strSql)

MsgBox rs!vCount

rs.Close
Set rs = Nothing
End Function


If I run the actual saved query I get 3 but with my hardcode the rs!Count is
always -0-. Why will it not return for me the count like the query I made in
the query grid does?

Thank you for your help.

Steven

 

[Granny Spitz via AccessMonster.com]

If I run the actual saved query I get 3 but with my hardcode the rs!Count is
always -0-. Why will it not return for me the count like the query I made in
the query grid does?

Have you tried moving to the first record before reading it?

Set rs = CurrentDb.OpenRecordset(strSql)
rs.MoveFirst
MsgBox rs!vCount

 

[Guest]

That did it! Thank you very much.

Have you tried moving to the first record before reading it?

Set rs = CurrentDb.OpenRecordset(strSql)
rs.MoveFirst
MsgBox rs!vCount

 

[Granny Spitz via AccessMonster.com]

That did it! Thank you very much.

You're welcome, Steven. I'm pleased it's working for you.