CODEWITHSUNDEEP
sqlms-accessvba
LiamH
LiamH

Reputation: 1502

MS Access VBA query. Select followed by a count of the result

I have asked a similar question recently. However, I don't think I knew the extent of what I was wanting to accomplish with my VBA at that time. I am using Access 2010 and creating an on_click command within a form.

So my intention is to create a query, and the approach I was going to take was as follows:

varSQL2 = "SELECT * FROM Inventory WHERE Part_ID=" & rs!Part_ID & ";"
Set rs2 = db.OpenRecordset(varSQL2, dbOpenDynaset)
varStock_Level = rs2!Stock_Level +rs!Quantity

the rs!quantity and rs!Part_ID are from another query earlier in my code. stock_level and part_ID are fields in the Inventory table.

I now need to be able to create a query to count how many records came back as a result of varSQL2. so I can do something along the lines of:

varSQL2 = "SELECT * FROM Inventory WHERE Part_ID=" & rs!Part_ID & ";"
Set rs2 = db.OpenRecordset(varSQL2, dbOpenDynaset)
varStock_Level = rs2!Stock_Level +rs!Quantity
    if count >1 then 
      ....[code]....
    end if

I don't really know where to start with this, slightly confused my self with subqueries. I believe I need 2 queries not one. Any help is appreciated.

Upvotes: 1

Views: 443

Answers (2)

asdev
asdev

Reputation: 943

There are several ways:

  1. Create a query with COUNT function (similar as you did in refered question)
  2. Use your query, use the rs2.movelast and read count out with rs2.RecordCount

  3. Use domain count

    Dim lngRows As Long lngRows = DCount("*", "Inventory", "Part_ID='" & rs!Part_ID & "'")

Upvotes: 2

iDevlop
iDevlop

Reputation: 25272

From memory:

rs.movelast
debug.print rs.recordCount

You must always Movelast before using RecordCount. I your table/query is big, it can be time consuming.

Upvotes: 0

Related Questions