VBA in form to sum values from a subform and return in main form

[Mr_Si]

Hi all,

I have a quote form, pictured below:

It accesses data from these 2 tables:

tblQuote (the main one)

and tblQuoteItems (the subform which is as a datasheet style)

Basically, for all the items in the subform, they will each have a value in the subtotal column. I want to sum these items together where the idsQuoteID in the subform is equal to the idsQuoteID in the main form. I then want the summed value to be put into the Ex. VAT total in the main form (just below the datasheet).

I have no idea where to start on the code with this one, I just know it's do-able. So I'd appreciate pointers very much

The code would probably be entered into the onLostFocus() event of the subform so that as soon as the subform is no longer "active", the ex. VAT value of the quote would change.

I guess the logic would go something like:


FOR EACH form_frmQuoteItemsSubform.idsQuoteItemID

WHERE form_frmQuoteItemsSubform.idsQuoteID = me.QuoteID
(me is refering to the main form)

me.QuoteValue = SUM form_frmQuoteItemsSubform.curItemSubTotal

DO UNTIL form_frmQuoteItemsSubform.idsQuoteID is NOT equal to me.QuoteID


I hope that makes some sort of sense.

I look forward to your replies.

Blessings,

Simon Barrett

 

[Mr_Si]

Would I be right in assuming an amalgamation of a "FOR EACH" loop and a "Do While" loop?

I would then need to put all the values somewhere so I could sum them... Possibly a variable...? possibly a temporary table...?

 

[Rabbie]

Try looking in the Orders form in the northwind sample DB supplied with Access. There is something that looks like what you want to do there.

 

[Mr_Si]

Thank you Rabbie, I shall do that now

Edit: I can't seem to make heads or tails of it - The code seems to have no reference to the complete total on the order form. I can't see any calculation at all.

 

[Rich]

Is this an assignment?

 

[Mr_Si]

nope. It really isn't. It's an order entry database I'm trying to work on for work. I've got quite far, but am stuck on what really should be a simple thing.

Edit 2: ooooh, I'm so dumb, it's simply done in SQL - I found it in one of the queries.

 

[Mr_Si]

Ok, so since I started in this form, I will keep it here, even though it's SQL based now.

This is my new form, as you can see, it's got the #Name? in the control as I changed it to "=[qryQuoteValue]![QuoteValue]"

as can be seen by the query below, it returns the sum with no problem, into the field which I've referenced as the control source in the form. But for some reason, it's not showing it in the form.

My SQL is as follows:

SELECT tblQuoteItems.lngzQuoteID AS QuoteNo, Sum(tblQuoteItems.curItemSubTotal) AS QuoteTotal
FROM tblQuote INNER JOIN tblQuoteItems ON tblQuote.lngzQuoteID=tblQuoteItems.lngzQuoteID
WHERE tblQuoteItems.lngzQuoteID=tblQuote.lngzQuoteID
GROUP BY tblQuoteItems.lngzQuoteID;



As can be