RecordCount returning the incorrec number of records

  • Thread starter Thread starter MichaelJohnson168
  • Start date Start date
M

MichaelJohnson168

I am using Access 2000 and I have a query that returns only records
where the order is "Open". I have been attempting to open that query in
a recordset, then count the number of records in that recordset, thus
letting me know how many orders are open and in front of the current
customer.

The problem is that the RecordCount is returning 1 as the number of
records in the recordset. Is this because queries are not to be used
with recordsets?

I am trying to avoid having to open the "Orders" table in a recordset,
then looping through each record, then checking the "Order Status"
field, and then finally counting the records that are open.


Code Below
----------
Dim queue_no_of_records As Integer

Dim db As DAO.Database
Dim queueRecordset As DAO.Recordset
Set db = CurrentDb
Set queueRecordset = db.OpenRecordset("I_Open_Phone_Orders")

queue_no_of_records = queueRecordset.RecordCount
 
RecordCount isn't guaranteed to be correct until you've read all records.

Try:

Dim queue_no_of_records As Integer

Dim db As DAO.Database
Dim queueRecordset As DAO.Recordset
Set db = CurrentDb
Set queueRecordset = db.OpenRecordset("I_Open_Phone_Orders")
queueRecordset.MoveLast
queue_no_of_records = queueRecordset.RecordCount

although unless you're using that recordset for some other purposes, it
would probably make more sense just to use:

Dim db As DAO.Database
Dim queueRecordset As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Count(*) AS TotalOrders " & _
"FROM [I_Phone_Orders] " & _
"WHERE OrderStatus='Open'"

Set db = CurrentDb
Set queueRecordset = db.OpenRecordset(strSQL)
queue_no_of_records = queueRecordset!TotalOrders
 
Back
Top