RecordCount of recordset containing specific field 2

[EddyLLC]

Is it possible to find the recordcount of a recordset that contains a specific field. I mean if a recordset lists multiple records for 5 different invoices can I, through code, get the number of records for invoice number 2?

 

[JeremyNYC]

Yeah, in code. You'll have to walk the recordset. If you check out the help files, you'll see examples where they loop through (walk) the recordset. Copy most of that code, but inside the loop, which is where things happen once for each recordset, do something like this:

if rst("InvoiceNumber") = 2 then intCount = intCount + 1

You could, of course, replace that 2 with a variable that gets the specific invoice number you were looking for from a form or input box.

If you're looking to count how many records for each invoice in the recordset, you're probably better off building a different recordset to do just that.

Jeremy

---
Jeremy Wallace
METRIX Lead Developer
Fund for the City of New York
http:// metrix . fcny . org

 

[Golom]

Use another recordset

rs.Filter = "[InvoiceNumber] = 2"
Dim rsFiltered As DAO.Recordset
Set rsFiltered = rs.OpenRecordset()
rsFiltered.MoveLast
msgbox rsFiltered.RecordCount

 

[EddyLLC]

Thanks for the responses. Golom, I tried the following code based upon your suggestion. As you can see, qry1 has a parameter. The code went through without errors but the count was incorrect. In essence it did not apply the filter. It gave me the entire recordcount instead of only the ones where the invoice number (Inv) equals the variable strInv which I set to the invoice number earlier in the code. Any suggestions?

Dim rsFiltered As DAO.Recordset
Dim qdf17 As DAO.QueryDef
Dim prm17 As DAO.Parameter
rs.Filter = "[Inv] = strInv"
Set qdf17 = db.QueryDefs("qry1")
Set prm17 = qdf17.Parameters(0)
prm17 = strFile
Set rsFiltered = qdf17.OpenRecordset()
rsFiltered.MoveLast
count17 = rsFiltered.RecordCount
rsFiltered.Close

 

[Golom]

Perhaps I wasn't clear. From your original statement "... a recordset lists multiple records ...". That made me assume that you already had a recordset that contained multiple invoice numbers. In my response I assumed that original recordset to be called "rs". Adapting your code to use that convention.

    Dim rs As DAO.Recordset
    Dim rsFiltered As DAO.Recordset
    Dim qdf17 As DAO.QueryDef
    Dim prm17 As DAO.Parameter
    
    Set qdf17 = db.QueryDefs("qry1")
    Set prm17 = qdf17.Parameters(0)
    prm17 = strFile
    [COLOR=red]Set rs = qdf17.OpenRecordset()
    rs.Filter = "[Inv] = 2"
    Set rsFiltered = rs.OpenRecordset()[/color]
    rsFiltered.MoveLast
    count17 = rsFiltered.RecordCount
    rsFiltered.Close

 

[EddyLLC]

Thanks Golom. Looking at it after a few hours sleep I can see I completely missed it. Thanks for your patience. The code works fine.

 

[JeremyNYC]

Golom,

That's very cool. I never knew about the filter property of a recordset, or that you could build one on top of the other. Thanks much for the lesson.

Jeremy

---
Jeremy Wallace
METRIX Lead Developer
Fund for the City of New York
http:// metrix . fcny . org