Total sum of Items

[GazzaG]

I have a form which has 9 fields in which you enter the value of up to 9 items (value1 , value2 etc) how can I incorporate another field which which will give me a running total, I also want to add this to my report which will be the easy part I guess by just including that field in the query. thank you.

 

[pullingteeth]

Here's a code snippet which you should be able to adapt. Here I keep running totals for each "row" and "column", and then a grand unified running total which is consistency checked.

Private Sub Text28_AfterUpdate()
    total
End Sub

Private Sub Text29_AfterUpdate()
    total
End Sub

Private Sub Text30_AfterUpdate()
    total
End Sub

Private Sub Text31_AfterUpdate()
    total
End Sub

Private Sub Text32_AfterUpdate()
    total
End Sub

Private Sub total()
    totalacross Text12, Text19, Text26, Label46
    totalacross Text13, Text20, Text27, Label47
    totalacross Text14, Text21, Text28, Label48
    totalacross Text15, Text22, Text29, Label49
    totalacross Text16, Text23, Text30, Label50
    totalacross Text17, Text24, Text31, Label51
    totalacross Text18, Text25, Text32, Label52
    totaldown Text12, Text13, Text14, Text15, Text16, Text17, Text18, Label56
    totaldown Text19, Text20, Text21, Text22, Text23, Text24, Text25, Label57
    totaldown Text26, Text27, Text28, Text29, Text30, Text31, Text32, Label58
    sumsides
End Sub

Private Sub totalacross(textA As TextBox, textB As TextBox, textC As TextBox, labelA As label)
    labelA.Caption = Val(textA.value) + Val(textB.value) - Val(textC.value)
End Sub

Private Sub totaldown(textA As TextBox, textB As TextBox, textC As TextBox, textD As TextBox, textE As TextBox, textF As TextBox, textG As TextBox, labelA As label)
    labelA.Caption = Val(textA.value) + Val(textB.value) + Val(textC.value) + Val(textD.value) + Val(textE.value) + Val(textF.value) + Val(textG.value)
End Sub

Private Sub sumsides()
    Dim x As Double
    Dim y As Double
    x = Val(Label46.Caption) + Val(Label47.Caption) + Val(Label48.Caption) + Val(Label49.Caption) + Val(Label50.Caption) + Val(Label51.Caption) + Val(Label52.Caption)
    y = Val(Label56.Caption) + Val(Label57.Caption) - Val(Label58.Caption)
    If (x <> y) Then
        MsgBox "usermngmt_hourtracking: bug 1: bottom does not equal side. Please tell DB Admin", vbCritical
        MsgBox "x = " & x
        MsgBox "y = " & y & " " & Label56.Caption & "+" & Label57.Caption & "-" & Label58.Caption
     End If
    Label59.Caption = x
End Sub

 

[GazzaG]

Thank you for the swift reply, and the code looks great, but unfortunately it means nothing to me, is there any chance i could send you my file to view? I would appreciate it, Im new to access VB coding.

 

[pullingteeth]

I don't really have time to do that, but here's a simplified explanation.

Assume your textboxes are named Text1 ... Text9.

You will need to define an AfterUpdate event for each textbox (right click, properties, events, after update, choose [event procedure]); your code in each sub should be "total", as in:

Private Sub Text32_AfterUpdate()
    total
End Sub

Now you need to define a sub called total, which will do the actual work. Assuming you want the total to be displayed in Label1:

Private Sub total()
    Label1.caption = Val(nz(text1.value)) + Val(nz(text2.value)) + Val(nz(text3.value)) ...
End Sub

 

[hymn]

Control Source of the TotalField
[value1]+[value2]+[value3]+etc
will give you the total

Hope this helps
Hymn

 

[GazzaG]

Hymn that was brilliant, I owe you big time, just one thing I had to insert the "=" sign to start with to validate the expression, thanks once again.