Record count wrong (1 Viewer)

[John thomas]

Hi
im New to record sets
and have copied this code as a test

Option Explicit

Public Sub LoadArray()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("chimneysweep")

With rs
Debug.Print .RecordCount
.Close

End With

Set rs = Nothing
Set db = Nothing


In my immediate windoww it shows 1 record there are in fact 321 what am i doing wrong
I have tried different table still it only returns 1 record
many thanks john

 

[jdraw]

You have to do
.Movelast

before you get an accurate recordcount.

Option Explicit

Public Sub LoadArray()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("chimneysweep")

With rs
[COLOR="Blue"].movelast[/COLOR]
Debug.Print .RecordCount
.Close

End With

Set rs = Nothing
Set db = Nothing

 

[MarkK]

A recordset does not necessarily fully populate itself after opening. Common practice to count all the records would be to move the current record pointer to the last record using the recordset's .MoveLast method. Consider the follow code fragment...

with currentdb.openrecordset("someTable")
   if not .eof then .movelast
   msgbox .recordcount
   .close
end with

hth

 

[John thomas]

Thanks guys yes movelast does the job
regards john