RecordCount Question.

[JD]

I have set up code to determine the number of records in a table. I am using the RecordCount property of a Recordset object. However, this code returns <number of records - 1> instead of the total number of records. Does anyone have any ideas why this may be occurring

Thanks!

 

[Andrew Smith]

Could you post a bit more information on the type of recordset you are
using - eg DAO or ADO, dyanset, snapshot etc.

Also could you clarify what the number of records returned is as I can't
work out if you are getting -1, 1 or the true number of records minus 1!

Some ADO recordsets are not able to return a record cound. DAO Dynaset
recordsets will not give an accurate record count unless you force all the
records to open by moving to the end of the recordset with the .MoveNext
method.

I have set up code to determine the number of records in a table. I am

using the RecordCount property of a Recordset object. However, this code

 

[JD]

It is a DAO.Recordset opened as a table type (dbOpenTable). There are 22 records in the table and the rst_data.RecordCount call returns 21. I forced the object to access the last record (rst_data.MoveLast) and this did not change the result. I also tried looping through the table using .MoveNext and this did not work either. Any thoughts would be greatly appreciated.

Thanks!

 

[Andrew Smith]

I don't think you need to move to the end of the recordset in a table type
recordset, and when I've tried to do this it works fine.

How do you know there are 22 records in the table? Stupid question I know,
but are you including the "empty" new record at the end of the table (eg if
you put the cursor at the bottom of a table containing 21 records the
navigation controls will show "record 22 of 22" even though there are only
21 records).

It is a DAO.Recordset opened as a table type (dbOpenTable). There are 22

records in the table and the rst_data.RecordCount call returns 21. I forced
the object to access the last record (rst_data.MoveLast) and this did not
change the result. I also tried looping through the table using .MoveNext
and this did not work either. Any thoughts would be greatly appreciated.

 

[JD]

I double checked my source table and there are 22 records. I turned Options Explicit on in my form and that fixed the problem. I am not sure how that could affect this problem but it works...

Thanks for the help, Andrew.

 

[TC]

RecordCount is clearly documented. It will >not< necessarily return the # of
records in the table, snapshot, recordset, or whatever, unless & until you
do a movelast. he fact that it >seems< to return the right value, in certain
cases, without the movelast, is entirely coincidental, nd could change in
any later version of Access.

HTH,
TC

I double checked my source table and there are 22 records. I turned

Options Explicit on in my form and that fixed the problem. I am not sure
how that could affect this problem but it works...

 

[Marshall Barton]

RecordCount is clearly documented. It will >not< necessarily return the # of
records in the table, snapshot, recordset, or whatever, unless & until you
do a movelast. he fact that it >seems< to return the right value, in certain
cases, without the movelast, is entirely coincidental, nd could change in
any later version of Access.

Not so TC. A DAO table type recordset is supposed to return
the correct number of records without the MoveLast.

 

[TC]

Not so TC. A DAO table type recordset is supposed to return
the correct number of records without the MoveLast.

Yes, you are right.

"Returns the number of records accessed in a Recordset object, or the total
number of records in a table-type Recordset or TableDef object".

I knew the first part by memory. I had forgotten (or perhaps never read) the
second part! :-(

TC