Counting Records in a Recordset

[davecapone]

Is there a way to count the number of records in a recordset without having to loop through all the records and counting them that way? I'm guessing that there must be something like rd.count or something like that to return the count. I've tried using the COUNT command in my SQL statement but it won't let me retrieve all the fields that I need if I use it. My current SQL statement looks like:
mySQL="SELECT [Regional Contact Info].FName, [Regional Contact Info].MI," & _
" [Regional Contact Info].LName, [Regional Contact Info].RSID," & _
" [Regional Site Info].IDNum, [Regional Site Info].ContactID," & _
" COUNT([Regional Contact Info].RSID) AS NumContacts" & _" FROM [Regional Contact Info] LEFT JOIN [Regional Site Info]" & _
" ON [Regional Site Info].ContactID=[Regional Contact Info].RSID" & _
" GROUP BY [Regional Site Info].FName, [Regional Contact Info].MI," & _
" [Regional Contact Info].LName, [Regional Contact Info].RSID," & _
" [Regional Site Info].IDNum, [Regional Site Info].ContactID" & _
" ORDER BY [Regional Contact Info].LName"


Is there anyway to count these records?

Thanks,
David Capone

 

[NevG]

Dave

I may not be too far off the mark by suggesting

recordset.recordcount

i know I have used this in my ASP pages and have found it towork. I apologise if this does not fix your problems but thought Id try anyway

See ya

Nev

 

[davecapone]

Unfortunately, I've tried this and it always returns -1 when I know there are records being returned as the error is generated within a loop that tests for the recordset.eof

 

[jofarrell]

Dave,

I know in VB itself you sometimes have to .movelast .move first to get the correct record count.


Chris also gave me a helpful hint of rs.getRows() which may do the same thing for you.

Hope that helps.

Joanne

 

[vasah20]

dave -
you need to set the cursor type of the recordset in order to use .recordcount. try using the cursor type adOpenStatic (or any cursor that supports backwards movement).


good luck
leo

 

[DougP]

Try making your SQL statement smaller (return a less restrictive set of records) and see if you get another number in Recordcount
Also I force it to get the records with this little trick

recordset.Movelast
recordset.Movefirst
NumRecs = recordset.Recordcount

PS can you post more of your code here??? DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[davecapone]

Thanks everyone, my problem was with my cursor type. I didn't even think about it.


Thanks,
Dave Capone