Calculation Query

[steve09]

I need to calculate the total cost of the order and enter this into the "CostOfOrder" field which you can see in the picture...

I need to read all the orderlines for an order into a record set and calculate the cost for each orderline. I need to use a loop in VBA to add them all together.

Here is the query for inclusion in VBA...

Dim StrSQL As String

StrSQL = "SELECT orderline.qty*sandwich.price"
StrSQL = StrSQL + "FROM sandwich INNER JOIN orderline ON
sandwich.sno=orderline.sno"
StrSQL = StrSQL + "WHERE (((orderline.orderno)="
StrSQL = StrSQL & orderno
StrSQL = StrSQL + "));"

Do Until recordSetData.EOF
Total = Total + recordSetData.Fields(0).Value
recordSetData.MoveNext
Loop

I think the Query is correct, but where do I put this in the form (or where do I actually put it as I don't know!) to make it work?

Thanks in advance!
Steve

 

[llkhoutx]

The order detail appears to be a subform, you need to extend a price to each item in the order on the both the SandwichNo and Quantity AfterUpdate event, keeping a running sum in the subform footer. On the footer lost focus event store the footer total in your main form.

I don't think that your query is correct. "orderline.qty*sandwich.price" doesn have a field name. Querying isn't required to subtotal the subform.

The OrderNo is mererly the key for linking the form and the subform.