asked on

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

ASKER CERTIFIED SOLUTION

pdebaets

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pdebaets

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.

Sandra Smith

ASKER

Worked! Thank you.

Sandra

Sandra Smith

ASKER

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

Sandra