Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

RecordCount returning wrong number

Posted on 2008-10-09
6
Medium Priority
?
363 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

664 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