Using Access VBA, how to count number of output records in a select query and store the count as a VBA variable?

Question

I am using Access 2013, and in particular I'm stuck using the VBA code editor due to boss' instruction to minimise saved queries and tables - and also I don't have the access to modify table structure.

I have two tables: InvoiceDetails and AllItems, joined by ItemID.

INVOICEDETAILS     ALLITEMS
¯¯¯¯¯¯¯¯¯¯¯¯¯¯     ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
ItemID←-----------→ItemID
InvoiceNumber      Invoiced (Yes/No)

Sometimes an Item in InvoiceDetails can be individually credited, in which case the corresponding Invoiced in AllItems will be unchecked. I am trying to compare the

TotalItemCount = number of Items with a given InvoiceNumber "1"

with

InvoicedItemCount = number of Items with the given InvoiceNumber "1" that are still Invoiced

For the first number, I have

TotalItemCount = DCount("ItemID", "InvoiceDetails", "InvoiceNumber = 1")

For the second number, I have a SELECT query that gives me the count in SQL but I don't know how to convert that into the VBA variable InvoicedItemCount.

SELECT Count(InvoiceDetails.ItemID) 
FROM InvoiceDetails INNER JOIN AllItems 
     ON InvoiceDetails.LoadID = AllItems.LoadID 
WHERE InvoicedDetails.InvoiceNumber = 1 AND AllItems.Invoiced = True);

I know of DoCmd.RunSQL but AFAIK that just runs the query and doesn't give an output-able integer to be stored as a VBA variable. Is there a way to do this solely in VBA?

I think this is a duplicate of: stackoverflow.com/q/23992226/42346 — mechanical_meat
– mechanical_meat, Commented Mar 19, 2017 at 7:07

Community · Accepted Answer · 2017-05-23 12:10:16Z

2

I obtained the solution from this question, to which I was directed by Bernie.

Assuming that the SQL query returns one record, with only one field, and we want that single value:

Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT x FROM y WHERE z=z;"
Set rst = CurrentDb.OpenRecordset(strSQL)

VariableName = rst.Fields(0)

rst.Close
Set rst = Nothing

edited May 23, 2017 at 12:10

CommunityBot

11 silver badge

answered Mar 20, 2017 at 2:45

Secretss

531 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using Access VBA, how to count number of output records in a select query and store the count as a VBA variable?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related