Totals Query with criteria set at runtime in VBA

[Leigh46137]

I’m working on an Access 2007 database. The table named “Transactions” contains the fields: “TranType” (text), “Amount” (currency) and “ClientID” (number).

The query named “qTransactionSumm" groups by TranType and sums the Amount.

When the user selects a client on a form (lstSelection combo box), I want to run the query selecting only the transactions for the selected client, then plug the total invoices and total receipts into text boxes on the form. Relevant code looks like this:

stSQL = "SELECT * FROM [qTransactionSumm] WHERE [ClientID] = " & Me.lstSelection
Set rs = db.OpenRecordset(stSQL)
Do While Not rs.EOF
Select Case rs![TranType]
Case Is = "Invoice"
Me.txtInvoiceTotal = rs![SumOfAmount]
Case Is = "Receipt"
Me.txtReceiptTotal = rs![SumOfAmount]
End Select
rs.MoveNext
Loop

In the query, I tried adding the ClientID field and setting the “Total” line to “Where”, but when I save the query, it removes that field – preventing me from running the query with the where clause.

I tried setting the criteria line for ClientID to 1 in the query and saving it, but when I run the above code, it says “Too few parameters. Expected 1” when opening the recordset.

Anyone know a way around this?

 

[PNGBill]

Why don't you have the original query in your vba code?
To do what you are doing you need to get back to your original query.

You appear to be trying to force a criteria into qTransactionSumm when it is already running.

If you have qTransactionSummas your query in the vba code and include the variable then it should work.

Post your sql qTransactionSumm if you need help on this.

 

[Leigh46137]

The qTransactionSumm is not 'already running'. It is a saved query that would return the sum of all invoices and all receipts for all clients. I want to just run the query but add criteria to return the totals for only one client.

This method works perfectly for normal selection type queries, but Access chokes when the query has summarized fields and you want to specify selection criteria at runtime.

I was hoping to avoid coding the whole query in SQL in my form's VBA module because that can get hard to read and follow later. The queries also tend to run a bit faster if you run a saved query and just append the WHERE clause. Looks like I might not be able to do that with the summarized queries, though.

 

[PNGBill]

Could you make another query that will be a record source for your main totals query and this is the query your vba sets the criteria for?
This would mean 1 more query but you will avoid the issue you have with influencing a totals query.