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?

Answer 1

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