Recordcount question 1

[Maquis]

Can anyone tell me why this code does not work properly? Here it is in it's simplest format. My table contains about 4,000 records, however the textbox on the form always displays a count of 1.

    Dim strSQL As String
    Dim dbs As Database
    Dim rs As Recordset
    
    strSQL = "SELECT * from table1"
    Set dbs = CurrentDb()
    Set rs = dbs.OpenRecordset(strSQL)
    Me!TotalClaims = rs.RecordCount
    Set rs = Nothing
    Set dbs = Nothing

TIA Maq
<insert witty signature here>

 

[LarryDeLaruelle]

Maq:

I've found that the record count can be incorrect if you do not move the pointer after creating the recordset.

I'd try doing an rs.movelast just before the recordcount line. That should force a correct response/count. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[Maquis]

Thanks, that did the trick. That really is odd that Access can't do a record count without moving through the records first. I can understand having that restriction with linked tables, but this one is an internal table within the database. Maq
<insert witty signature here>

 

[beetee]

Actually, I can't understand why that doesn't work *ever*. It's like an operating system not knowing the file size.

 

[LarryDeLaruelle]

Maq and beetee:

I'm not sure, but I think I remember someone saying it has to do with the way Access uses 'pages' to handle data.

Do any of the Gurus have an answer? Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[JeremyNYC]

Well, it's certainly not an explanation of why, but this page contains some really good info on recordsets:

http://users.bigpond.net.au/abrowne1/ser-29.html

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.