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
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