Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

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

0
ssmith94015
Asked:
ssmith94015
  • 2
  • 2
1 Solution
 
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
 
ssmith94015Author Commented:
Worked!  Thank you.

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

Sandra
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!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now