Solved

RecordCount returning wrong number

Posted on 2008-10-09
6
346 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

743 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

11 Experts available now in Live!

Get 1:1 Help Now