• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

RecordCount returning wrong number

The code below returns the wrong value for recordcount. It returns 1 and should be 3. Does anyone see an obvious problem?

Dim db As Database
Dim rst As DAO.Recordset
   
    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT * FROM Temp_Sub_op_Time")
    If rst.RecordCount = 1 Then
        DoCmd.Close acForm, "frm_timeadj"
        DoCmd.OpenQuery "Del_temp_sub_op_Time"
        rst.Close
        Set rst = Nothing
        Exit Function
    End If
    rst.Close
    Set rst = Nothing
0
dgravitt
Asked:
dgravitt
1 Solution
 
mbizupCommented:
RecordCount is not the total count of records in your recordset. It is the absolute position.
 
 


To get the total number of records, you need to move to the last record, and then test recordCount.
 
 


ie:

rst.MoveLast
TotNumRecords = rst.recordcount
rst.MoveFirst
If TotNumRecords = 1 then...
0
 
dgravittAuthor Commented:
Duh, Thanks. Actually, going back and reading my book, it says that. Sometimes, I just read what I want to. Thanks for the help.
0
 
calpurniaCommented:
Just to clarify, RecordCount is not the current position within your recordset, but it doesn't pick up its correct value until you've moved to the end of the recordset. So in the above example, assuming you've got 25 records in your recordset:

Set rst = db.OpenRecordset("SELECT * FROM Temp_Sub_op_Time")
At this point, rst.RecordCount=1

rst.MoveLast
rst.Recordcount now=25

TotNumRecords = rst.recordcount
rst.MoveFirst
rst.RecordCount is still=25, even though you've now gone back to the start of the recordset
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
dgravittAuthor Commented:
Thanks for the clarification!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Note that the only valid test using RecordCount - not to be confused with getting the record count - is testing it against zero:

IF rst.RecordCount = 0 Then
   'whatever
Else
  'whateverElse
End If

And you should always test for zero *before* executing a MoveLast or any Move because if it is zero, you will get an error on the Move.

mx
0
 
dgravittAuthor Commented:
Thanks MX, actually, I needed that.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now