Add together a running total on input fields in a form?

[KevinSlater]

Hello, I have 4 input field boxes in a form: "basic hours", "overtime hours", "holiday hours" and "total hours" i would like it so that when the user enters number(s) into the other field(s) the total field will calculate (add togther the other fields). So for example if the user puts "10" in the "basic hours" input field and "5" in the overtime hours" field then the "toal field" will display the number "15". i would like it so that the "total hours" field calculates the total progressively after each number in the other fields is input (ie a running total is displayed). The user will always fill in basic hours but 1 or more of the other fields can be left blank. I think i need to do some VB on the after update procedure for each field entry but not sure what the code is?, any help would be brilliant.

 

[Dwight]

If I read your post correctly this should do it:

Create an unbound textbox and for it's control source put:

= nz([basic hours],0) + nz([overtime hours,0]) + nz([holiday hours],0)+ nz([total hours],0)

Where each of these is the 4 other text boxes.

Dwight

 

[KevinSlater]

Thanks for this dwight, however ive tired what you said & i cant get it to work. In the unbound text box i created for the total it says "#name?" when i open the form, ive checked the names of all the text boxes (and they match the names in the code), i persume code only needs to go in the control source of the unboud text box right?, any suggestions what i need to do to fix this?

 

[Dwight]

Sorry for the confusion,

The expression should only be adding = nz([basic hours],0) + nz([overtime hours,0]) + nz([holiday hours],0)

The total is equal to the sum of these three. So it's control source should use this expression.

Dwight

 

[KevinSlater]

Ok thanks a lot dwight, its working fine now, silly me i should have guessed that! however because the field of the total is now unbound it wont put the the total number into the table, (its always 0) anyone know how i can fix this?

 

[KevinSlater]

Ok, i fixed this by using "Let" statements on the after update of each field i wanted the total to calculate, this enabled the total field to be saved in the table, the code i used below works.


Private Sub BASIC_HOURS_WORKED_AfterUpdate()
Let [TOTAL HRS] = [BASIC HOURS WORKED]
End Sub

Private Sub O_TIME_HOURS_AfterUpdate()
Let [TOTAL HRS] = [BASIC HOURS WORKED] + [O_TIME_HOURS]
End Sub

Private Sub HOLIDAY_AfterUpdate()
Let [TOTAL HRS] = [BASIC HOURS WORKED] + [O_TIME_HOURS] - [HOLIDAY]
End Sub

 

[Dwight]

Glad you got it working but just so you know, it is considered "poor practice" to store calculated fields in a table. It is much better to perform calculations in queries because if the input data in the tables changes the query calculation will reflect these changes but any calculation in a table will not.

Something to consider.