RecordCount Returning the wrong value

[Matt]

I am using the dao.recordset object to hold a set of data. I run a
query that I know returns 5 rows and store it into into the recordset
object (objrst). When I output the .Recordcount it always gives me 1
(rather than 5). It works fine for with other queries, but gives me
problem with just one query.

Code:

Dim acquery As DAO.QueryDef
Dim objrst As DAO.Recordset
Dim i As Integer

Set acquery = CurrentDb.QueryDefs("a")
Set objrst = acquery.OpenRecordset

MsgBox (objrst.RecordStatus)


Here is the query "a":
SELECT TOP 5 MastrQualityTbl.ERROR_TYPE, Count(MastrQualityTbl.USERID)
AS CountOfUSERID
FROM MastrQualityTbl
WHERE (((MastrQualityTbl.ERROR_TYPE)<>"" And
(MastrQualityTbl.ERROR_TYPE)<>"post cycle") AND
((MastrQualityTbl.USERID)="v3pm2a"))
GROUP BY MastrQualityTbl.ERROR_TYPE
ORDER BY Count(MastrQualityTbl.USERID) DESC;


Does it have to do with the TOP 5 option, grouping, or sorting?



Help is GREATLY appreciated.

 

[Jezebel]

This is not the right forum to ask.

I think the problem is the Count() part of the query: that returns only one
value, not five. My recollection is that DAO can't do disjoint queries like
this. You need a JOIN to relate the two tables.

A way to check what's going on is to open the database in Access, paste the
query into the SQL window, and execute it there.

 

[Tony Jollans]

I think you may need to do a .MoveLast (followed by a MoveFirst to get back
to the beginning if you need to) to force the recordset count to be correct.

 

[Matt]

I think you may need to do a .MoveLast (followed by a MoveFirst to get back
to the beginning if you need to) to force the recordset count to be correct.

twice you bailed me out Tony
Thanks!

 

[Tony Jollans]

My pleasure!

--
Enjoy,
Tony

twice you bailed me out Tony
Thanks!