RecordCount property returns (1)

[JimP]

I have a query ("ReportNames") that displays (3) records when opening it,
yet the code below shows NbrRecs =1.

How can this be?

Sub Testing1()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("ReportNames")
NbrRecs = rs.RecordCount
Set rs = Nothing
End Sub

 

[Stuart McCall]

I have a query ("ReportNames") that displays (3) records when opening it,
yet the code below shows NbrRecs =1.

How can this be?

Sub Testing1()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("ReportNames")
NbrRecs = rs.RecordCount
Set rs = Nothing
End Sub

The recordcount property reflects the number of records *visited* in the
recordset. So you need to move to the last record before reading it:

Set rs = db.OpenRecordset("ReportNames")
rs.MoveLast
NbrRecs = rs.RecordCount

 

[Linq Adams via AccessMonster.com]

You (sometimes) need to move to the last record then back to the first record


Set rs = db.OpenRecordset("ReportNames")

rs.MoveLast
rs.MoveFirst

NbrRecs = rs.RecordCount

Have no idea ***why*** you only have to do this sometimes, but not always! As
Jack Webb used to say "Just reporting the facts, ma'am!"

 

[JimP]

...odd indeed!

 

[John Spencer]

Well, the number reported by recordcount is the number of records that have
been loaded when rs.RecordCount is called.

Normally, that will be zero or one until you do something with the recordset.
If you want a count of all the records in the recordset, the best thing is
to move to the end of the recordset. Get the count and then move back to the
first record in the recordset. Or just do as the posted code shows.

If all you need to know is if the rst has records then you can use

If rs.Recordcount > 0 Then
....

OR check rs.bof and rs.eof to see if they are both false to determine if there
are any records in the recordset.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Mark Ransom]

Just for interest.

I have found that if there are any records in a recordset, it is
necessary to "populate the recordset" by

rs.MoveLast
rs.MoveFirst

If you don't, you get a record count of one if there are any records in
the recordset. I started using VBA in Access 95, and it has been the
same since then.

If there are no records (rs.RecordCount=0), you cannot execute those
commands, so I always use an IF...ELSE...END IF whenever I create a
recordset to test and populate if appropriate.

MPR