record set count?

[xq]

i wrote a query and ran the sql, i tried to use rs.recordcount to find out how many record there r, but i always got 1, i checked the query, it difinitely returns more than 2 records, why this function doesn't work? any body could help me to have a look my code? thanks a lot!

.....
Set db = CurrentDb
sql = ""
sql = "SELECT ID " & _
"FROM Invoice " & _
"WHERE Payment_No = 2"
Set rs = db.OpenRecordset(sql)
r = rs.RecordCount
Me!txt = r
rs.Close
db.Close

 

[oharab]

the recordcount function only counts the records you have accessed, so you need to go all the way over the recordset to count all the records:

On error resume next 'to catch if no records in set
rs.movelast
rs.movefirst
on error goto 0 'reset error trapping

msgbox rs.recordcount

I've never been able to work out if it's a bug or an intended feature!

HTH

Ben ----------------------------------------
Ben O'Hara
Home: bpo@SickOfSpam.RobotParade.co.uk
Work: bo104@SickOfSpam.westyorkshire.pnn.police.uk
(in case you've not worked it out get rid of Sick Of Spam to mail me!)
Web:

http://www.RobotParade.co.uk

----------------------------------------

 

[xq]

great, it works! thanks million!