RecordCount on recordset only returning 1 rather than number of rows

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

Sandra SmithRetiredAsked:
Who is Participating?
 
pdebaetsCommented:
Try replacing this line of code

intCount = rsEmployees.RecordCount

Open in new window


with this

rsEmployees.movelast
intCount = rsEmployees.RecordCount
rsEmployees.movefirst

Open in new window

0
 
pdebaetsCommented:
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.
0
 
Sandra SmithRetiredAuthor Commented:
Worked!  Thank you.

Sandra
0
 
Sandra SmithRetiredAuthor Commented:
Thank you also for the explanation, I was thinking something like that but you confirmed.

Sandra
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.