Automatically calculate sum for each current record

[onusonu]

I have a form that calculates the cost( Cst1, Cst2, Cst3...Cst13)of each items that goes into, say pizza. The price changes constantly. I have to click "calculate" every time when I view the formulas to update the pricing. How do I get the total pice automatically reflect the new cost of each ingredients without hitting the "calculate" button.

I tried setting the Control Source of CstTotal(total cost)= Cst1+Cst2+Cst3...Cst13, and sum(Cst1+Cst2..) but it sum all the records not each record.


i=1 to 13
Per(i)=Percentage of each ingredient
Wt(i)=weight of each ingredients
Cst(i)=cost of each ingredient in ounce

Private Sub Calculate_Click()
Dim countT As Integer
Dim weight As Double, i As Integer
Dim cost As Variant, TotalCost As Double

On Error Resume Next

For i = 1 To 13

If Not IsNull(Me("Part" & CStr(i))) Then
Me("Per" & CStr(i)) = Format(TPercent(Me("Wt" & CStr(i))), "###.00")
weight = Me("Wt" & CStr(i))
Me("Cst" & CStr(i)) = Format((Cst(i) / 16) * weight, "###.000")
'MsgBox Cst(i) & "cst" & Me("Cst" & CStr(i))
TotalCost = TotalCost + Me("Cst" & CStr(i))
Else
Cst(i) = ""
Me("Wt" & CStr(i)) = ""
Me("Per" & CStr(i)) = ""
End If
Next i

TWeight = TotalW
CstTotal = TotalCost

End Sub


PJ

 

[Trevil]

Assuming you must enter a weight somewhere on your form, and each record has the 13 fields you mention, the lazy approach could be as simple as the following:
1. Add an 'After_Update' event for the weight field.
2. Insert a call to 'Calculate_Click'


Code: Where the vision is often rudely introduced to reality!

 

[onusonu]

The weight of an ingredient is fixed, but the pricing is not. Say, the price of romaine lettuce can increase or decrease depending on the weather and the demand. Some formula is made up of 10 items, other one 3 items. the price is retreived from another ingredienttable. When I update the price on the ingredienttable, the price will reflect in the formula table. And every time I have to click "calculate" button to update the sum. Other people will be using the form to view the formula and cost and they probably won't know to click calculate to update cost.
Any suggestion?

 

[Trevil]

Based on the sample code you had provided, I believe you have a form that shows some item (pizza) and all of the ingredients (cheese, sauce, etc.) and possibly the weight and cost of each ingredient.

AND you have some control that you want to show the TOTAL cost of all ingredients?

Now, are you worried about 'real-time' reflection of the most current prices for all your users, or only on getting the 'current' total cost for that session?

I'm not sure which situation you are trying to solve.

If it's a 'real-time' issue, then you will need a method of 'refreshing' the form view at some interval.

If it's just showing the total, that can be done as mentioned previously.

Please let me know.


Code: Where the vision is often rudely introduced to reality!

 

[onusonu]

Yes, I'm concern about 'real-time' reflection of the most current price.
For example form
Formulation Form
Item # Ingredient Weight % Price
it1 Ing1 Wt1 Per1 Cst1=Ing1.colum(1)
it2 Ing2 Wt2.. Per2 Cst2=Ing1.column(1)
it3 Ing3
it4..


The Ing(i)row source=select distinct ingredient, price from IngTable. And control source Cst(i)=Ing(i).column(1)

I update ingredient pricing in the IngTable, which then reflect in the Cst(i).

I want the total cost to recal without clicking the "calculate" button.

I tried TotalCost=sum(Ing1.column(1)+Ing2.column(1),etc)that the same sum for every record.

I hope I"m not confusing you.

thank you

 

[Trevil]

I must be missing something and think we need to go back to the start. I can figure out how to sum the items if I can understand how you are using the data.


Please respond to the following questions:

1. You have a table 'IngTable' which contains fields ingredient and price. Either these records are unique based on 'ingredient' field, or also have some other field like 'product' which combines to make unique records?

2. You have a table 'formula', which I assume must be related to 'IngTable' on some field. I don't know what fields are in that table, or if there is a cost field included. It appears you have at least 'Item#, Wt., Pct'?

3. You have a form that will show all ingredients for some product. Does this form have a 'detail' section that shows one line item per ingredient, or do you have just a bunch of text boxes that get filled in with ingredients? If you have a detail section that shows individual items, I assume you have a 'total cost' box in the form footer?

4. Do you use this same form to update prices, or to only display prices? If you update prices here, are there other controls on the form to allow the update, or do you just type into the detail section?

5. What are the table relationships you have defined? I assume either table 'IngTable' is a 'member' of the relationship with 'formula' or there is some other table that networks the connection (many-to-many).

You mention "Ing(i)row source=select distinct ingredient, price from IngTable", so I'm guessing you have some combo box, but not sure how you are using it.

If you have a detail section with rows for each ingredient, then we can easily sum the total.





Code: Where the vision is often rudely introduced to reality!

 

[onusonu]

1. Yes, the unique field is product name, but a product can have multiple formulas. Say the product is Pepperoni pizza 9", which I choose from a drop down combobox cmbProduct. Then I have 5 columns of 13 rows of blank txtbox. the first column is the cmbID. I select an ID and the ingredient name corresponding to that ID will fill the empty box of the 2nd column. then I enter the amount in oz/lb in the 3rd column I need of that ingredient. the 4th(%) column is blank. The 5th column (cost) is set to equal ingredient.column(1). So once an ingredient appear in the ingredient column, the price corresponding to that ingredient appear. Once I'm done enter data, I click the "calculate" button. That will calculate % of each ingredient and the sum of column 5 (cost).

2. i=1 to 13. The IngTable has txtID, txtIngredient, txtCstLb fields. Formula form has ProductName, txtID(i), txtIngredient(i), txtCstOZ(i), Wt(i), Percentage(i), TotalWeight, and TotalCost.

3. All boxes are in the detail section of the form. I place the TotalCost in the footer and tried assign it to TotalCost=sum(Cst1+Cst2+Cst3...) but that gives me the sum of all 55 records. So every record I scroll to has the same total sum.
4. I type in the price(txtCstLB) in the IngTable. In the formula form, I set txtIngredient=select distinct ingredient, price from IngTable. And txtCstOZ=txtIngredient.column(1)/16*Wt
5. I did not set any relationship

"Ing(i)row source=select distinct ingredient, price from IngTable"

ID is a combo box as well as Ing. If I know the ID number of an item, I select an ID and the ingredient name will pop up. Or if I don't know the ID, I select the ingredient and the ID will fill in. In either case, the price will autmatically fill in.

Hope that helps

thank you
pj

 

[Trevil]

I think I finally understand. You have 67 or more controls on the form (5 columns of controls with 13 rows = 65; total, and cmbProduct).

I'm assuming the first column is actually 13 comboboxes where you select ingredients. But when you say "The 5th column (cost) is set to equal ingredient.column(1)", I don't know where that control is (did you mean "cmbID.Column(1)"?)

Also, you are only performing two actions per ingredient: 1. Selecting the ingredient from the cmbID control, and 2. entering a weight.

If my assumptions are correct, then I believe you can just add an 'After_Update' event for each cmbID and for each txtWeight that calls your recalculate function.

Here's a sample of what I did:
Function Calc_All()
Dim i As Integer
Dim TotalWeight As Double
Dim TotalCost As Double
Dim Cost As Variant

For i = 1 To 13
If Not IsNull(Me("txtWeight" & i)) Then TotalWeight = TotalWeight + Me("txtWeight" & i)
If Not IsNull(Me("txtPrice" & i)) Then TotalCost = TotalCost + CDbl(Me("txtPrice" & i))
Next i
Me.txtTotalCost = Format(TotalCost, "$.00")
Me.txtTotalWeight = Format(TotalWeight, "#.0")
End Function




"Hmmm, it worked when I tested it....