Solved

RecordCount returning wrong number

Posted on 2008-10-09
6
347 Views
Last Modified: 2010-04-21
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
Comment
Question by:dgravitt
6 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 125 total points
ID: 22677719
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
 

Author Closing Comment

by:dgravitt
ID: 31504614
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
 
LVL 10

Expert Comment

by:calpurnia
ID: 22677932
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:dgravitt
ID: 22678028
Thanks for the clarification!
0
 
LVL 75
ID: 22679828
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
 

Author Comment

by:dgravitt
ID: 22680187
Thanks MX, actually, I needed that.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now