CODEWITHSUNDEEP
ms-accessrecordset
Les Programmer
Les Programmer

Reputation: 121

DAO.Recordset.RecordCount property not working as expected

I am running a query from input from a form. It is to extract records from 1 table, where the table is joined to 3 others.
It does successfully extract the record. In this case there is only 1 record and the query does find it. The problem is that every time I run this code, I get one more RecordCount. So the first time I run it, the RecordCount is 1 and debug.print gives me the correct information. The second time I run it, the RecordCount is 2 and debug.print gives me the correct information but twice. The third time ... 3 and 3, etc. It doesn't matter if I close the form (from which I get my variables) and reload it. The number keeps climbing. I closed Access and reopened it and the number keeps climbing - it didn't reset to 1 record found.

The query selects records depending on a ProductID where the (qtyordered - qtyproduced > 0) and the records are sorted by priority of the customers.

Dim rs As DAO.Recordset
    Dim db As DAO.Database

    Dim findCSQL As String

    findCSQL = "SELECT tblWarehouseTransfers.WTRProductID, tblOrderDetails.ODEPriority, tblOrderDetails.ODEQuantityOrdered, " _
                        & " tblOrderDetails.ODEQtyProduced, tblCustomers.CompanyName " _
                & " FROM  tblCustomers INNER JOIN (tblOrders INNER JOIN (tblWarehouseTransfers INNER JOIN tblOrderDetails " _
                        & " ON tblWarehouseTransfers.WTRProductID = tblOrderDetails.ODEProductFK) " _
                        & "  ON tblOrders.ORDOrderID = tblOrderDetails.ODEOrderID) ON tblCustomers.ID = tblOrders.ORDCustomerID " _
                & " WHERE (((tblWarehouseTransfers.WTRProductID) = " & Chr$(34) & Me!cboTransferProductID & Chr$(34) & ")) " _
                & " AND ((tblOrderDetails.ODEQuantityOrdered - tblOrderDetails.ODEQtyProduced)> 0) " _
                & " ORDER BY tblOrderDetails.ODEPriority "


    Set rs = db.OpenRecordset(findCSQL)

    rs.MoveFirst
    Debug.Print rs.RecordCount

    Do While Not rs.EOF
      Debug.Print ("product ID: " & rs!WTRProductID & " qty ordered: " & rs!ODEQuantityOrdered & " customer name:  " & rs!CompanyName)
      rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing

How can I empty the recordset and start fresh each time the subroutine is run?

Upvotes: 1

Views: 2501

Answers (1)

LarsTech
LarsTech

Reputation: 81610

My memory might be off since it's been a long time since I've worked with RecordSets, but I think you had to traverse the records before the RecordCount property gave you the correct information.

So try it this way:

rs.MoveLast
Debug.Print rs.RecordCount
rs.MoveFirst

From How to: Count the Number of Records in a DAO Recordset

The value of the RecordCount property equals the number of records that have actually been accessed.

Upvotes: 1

Related Questions