RecordCount on recordset only returning 1 rather than number of rows

Posted on 2011-10-13
Last Modified: 2012-05-12
I have a procedure that creates a query in memory and works on that.  However, I know that the select statement should have 13 rows, but when I do a rowcount, it only returns 1.
'Import Score and Personal Info
    sSQL = "SELECT * "
    sSQL = sSQL & "FROM EmployeeRepApprovalView "
    sSQL = sSQL & "WHERE ApprovalHistory.quarter=" & gEvalQuarter & " "
    sSQL = sSQL & "AND ApprovalHistory.year=" & gEvalYear & " "
    sSQL = sSQL & "AND ApprovalHistory.status='Approved' "
    Set rsEmployees = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
    intCount = rsEmployees.RecordCount
    Forms!frmProcessing.txtItems = intCount
    Do While Not rsEmployees.EOF
    intItem = intItem + 1
    Forms!frmProcessing.txtItem = intItem  'Update count of number of items processed

Open in new window

Question by:ssmith94015
    LVL 12

    Accepted Solution

    Try replacing this line of code

    intCount = rsEmployees.RecordCount

    Open in new window

    with this

    intCount = rsEmployees.RecordCount

    Open in new window

    LVL 12

    Expert Comment

    Explanation: Access doesn't know the number of records in a record set when the record set is opened. It is only when the last record is accessed that the number of records is know. So you have to use a .movelast method to get to the last record, then the .RecordCount property returns something sensible.

    Author Closing Comment

    Worked!  Thank you.


    Author Comment

    Thank you also for the explanation, I was thinking something like that but you confirmed.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    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!

    MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
    The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    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…

    759 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

    10 Experts available now in Live!

    Get 1:1 Help Now