Solved

RecordCount returning wrong number

Posted on 2008-10-09
6
353 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
Industry Leaders: 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!

 

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

Industry Leaders: 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!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

679 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