Help with editing field values that get summed up, resets the 'total' field

[bytreeide]

I have several fields that I fill in on a form and they all need to add up in the last field. The first way that I tried this worked good for the initial data entry on the form. However if I need to edit one of the fields in the form then it resets the TotalCost field and I have to enter all of the numbers again. Is there some way that I can make this work? Or is this an Access quirk I will have to live with?



I tried to do this two different ways.

Here is the code for the first method:

Option Compare Database
Dim C1 As Long 'For Total Cost
Dim C2 As Long 'For Total Cost
Dim C3 As Long 'For Total Cost
Dim C4 As Long 'For Total Cost
Dim C5 As Long 'For Total Cost
Dim C6 As Long 'For Total Cost
Dim C7 As Long 'For Total Cost

___________________________________________________________________ 
Private Sub Cost1_AfterUpdate()
C1 = Me.Cost1
Me.TotalCost = C1 + C2 + C3 + C4 + C5 + C6 + C7
End Sub

___________________________________________________________________ 
Private Sub Cost2_AfterUpdate()
C2 = Me.Cost2
Me.TotalCost = C1 + C2 + C3 + C4 + C5 + C6 + C7
End Sub

___________________________________________________________________ 
Private Sub Cost3_AfterUpdate()
C3 = Me.Cost3
Me.TotalCost = C1 + C2 + C3 + C4 + C5 + C6 + C7
End Sub

___________________________________________________________________  
Private Sub Cost4_AfterUpdate()
C4 = Me.Cost4
Me.TotalCost = C1 + C2 + C3 + C4 + C5 + C6 + C7
End Sub

___________________________________________________________________  
Private Sub Cost5_AfterUpdate()
C5 = Me.Cost5
Me.TotalCost = C1 + C2 + C3 + C4 + C5 + C6 + C7
End Sub

___________________________________________________________________  
Private Sub Cost6_AfterUpdate()
C6 = Me.Cost6
Me.TotalCost = C1 + C2 + C3 + C4 + C5 + C6 + C7
End Sub

___________________________________________________________________  
Private Sub Cost7_AfterUpdate()
C7 = Me.Cost7
Me.TotalCost = C1 + C2 + C3 + C4 + C5 + C6 + C7
End Sub

This way works great the first time that I tried it, I had to re-enter info in all the fields if I wanted to change one.

Here is my second method:

Which didn't work, i received an error message:
"The expression After Update you entered as the event property setting produced the following error: Invalid outside procedure."
And then nothing changes.

Option Compare Database
Dim C1 As Long 'For Total Cost
Dim C2 As Long 'For Total Cost
Dim C3 As Long 'For Total Cost
Dim C4 As Long 'For Total Cost
Dim C5 As Long 'For Total Cost
Dim C6 As Long 'For Total Cost
Dim C7 As Long 'For Total Cost
C1 = Me.Cost1
C2 = Me.Cost2
C3 = Me.Cost3
C4 = Me.Cost4
C5 = Me.Cost5
C6 = Me.Cost6
C7 = Me.Cost7
___________________________________________________________________ 
Private Sub Cost1_AfterUpdate()
Me.TotalCost = C1 + C2 + C3 + C4 + C5 + C6 + C7
End Sub

___________________________________________________________________ 
Private Sub Cost2_AfterUpdate()
Me.TotalCost = C1 + C2 + C3 + C4 + C5 + C6 + C7
End Sub

___________________________________________________________________ 
Private Sub Cost3_AfterUpdate()
Me.TotalCost = C1 + C2 + C3 + C4 + C5 + C6 + C7
End Sub

___________________________________________________________________  
Private Sub Cost4_AfterUpdate()
Me.TotalCost = C1 + C2 + C3 + C4 + C5 + C6 + C7
End Sub

___________________________________________________________________  
Private Sub Cost5_AfterUpdate()
Me.TotalCost = C1 + C2 + C3 + C4 + C5 + C6 + C7
End Sub

___________________________________________________________________  
Private Sub Cost6_AfterUpdate()
Me.TotalCost = C1 + C2 + C3 + C4 + C5 + C6 + C7
End Sub

___________________________________________________________________  
Private Sub Cost7_AfterUpdate()
Me.TotalCost = C1 + C2 + C3 + C4 + C5 + C6 + C7
End Sub

So I know this second method is incorrect.

Thanks for your help.

[pbaldy]

Disregarding the normalization problem implied by 7 cost fields, I would expect the first method to work, unless an error occurred that reset the variables. That said, why bother with them? I'd have a function and call it from all the after update events:

Me.TotalCost = Nz(Me.Cost1, 0) + Nz(Me.Cost2, 0) +...

[bytreeide]

pbaldy,

Thanks for the help. That totally worked and I eliminated the use of the variables in my code.

bytreeide

[pbaldy]

Happy to help!