Calculating totals

[Carl Johnson]

I do not know if this is the right news group to ask is and if it isn't
forgive me. I have a line item field with a corresponding field where entry
is made of the latest purchase. I have another field that reflects the total
annual budget for that line item.Can I and if so how do I when the end user
enters data into the purchase item field that change is deducted in the
total field and the purchase item field is then cleared until the process is
repeated? Long question sorry, but any help would be greatly appreciated.

 

[Allen Browne]

The best solution will to be NOT store the total items so far, but rather
the calculate the total when you need it.

This example assumes you have a combo box for the ItemID, and the 3rd column
of the combo gives you the CategoryID number for the item (i.e. which budget
category it belongs to). It sums the *other* items in the table (in case
this is an existing record that was changed), adds this item, provides a
warning, and allows the user to override the warning:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim curTotal As Currency
Dim strMsg As String

If Me.Amount = Me.Amount.OldValue Then
'do nothing: it is an existing record where the amount is unchanged.
Else
strWhere = "(CategoryID = " & Me.ItemID.Column(2) & ") AND (ID <> "
& Me.ID & ")"
curTotal = Nz(DSum("Amount", "MyTable", strWhere), 0) +
Nz(Me.Amount,0)
If curTotal > [WhateverYourBudgetTotalIsHere] Then
strMsg = "Over budget. Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2) <> vbYes Then
Cancel = True
'Me.Undo
End If
End If
CurTotal
End If
End Sub