Count records in the recordset (1 Viewer)

[dz2k7]

How to count records in the recordset?

I tried

rc = rs.RecordCount

but it returns -1 all the time

Thanks

 

[Kiwiman]

Howzit

after you have set the recordset try. I'm not sure if this is strictly necessary, but I have always done this.

rs.movelast
rs.movefirst
rc = rs.recordcount

 

[Banana]

^

Indeed. With DAO, the recordset is ready to use as soon as first record or page is loaded, so you don't have accurate count unless you move to the last record. This is quite expensive.

But you get -1, which leads me to suspect that you are using ADO or querying a linked table. If this is the case, I believe you cannot retrieve recordcount with that method, and may have to use a Count(*) query to get the count. There may be workarounds that I don't know about as well.

 

[Kiwiman]

Thanks for that Banana - at least I now know why moving to last is required.

 

[chergh]

If your using ADO you will need to change the cursor type so where you open your recordset it would be something like:

rs.open "SELECT * FROM TABLE", connection, adOpenStatic

rs.recordcount should return the correct recordcount with adOpenStatic specified.

 

[dz2k7]

Chergh you won!

That works fine.

Thanks a lot!

 

[gemma-the-husky]

if you have thie query as a stored query, which is generally more efficient anyway you can just do

dcount("*","myquery")

 

[dz2k7]

Cool!
That works too!
You guys are the best!