field calculation and comparison

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

Extreme access novice...please be patient!

I have 5 fields that I want to add together and then
compare that total to a value on a subform.

If the 5-field-total is not equal to the total on the
subform I want to make the entry person re-enter the
numbers on the 5 fields until the total is correct.

Any help???

Thanks!
 
Extreme access novice...please be patient!

I have 5 fields that I want to add together and then
compare that total to a value on a subform.

If the 5-field-total is not equal to the total on the
subform I want to make the entry person re-enter the
numbers on the 5 fields until the total is correct.
Click to expand...

You're going to have some VERY frustrated users! Access is very good
at calculating totals; why make the user do it!?

That said... be sure that the datatype of this field is either Integer
(if you don't need decimal places) or Currency. The Number datatypes
Float and Double allow decimal places, but are approximations; 0.2 +
0.2 + 0.2 + 0.2 + 0.2 is NOT necessarily equal to 1.0, but to
something like 0.999999999999983.

Then you'll need some VBA code in the Form's BeforeUpdate event, like:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me!txt1 + Me.txt2 + Me.txt3 + Me.txt4 + Me.txt5 <> _
Me!subformname.Form!controlname Then
MsgBox "The sums don't balance, try again", vbOKCancel
If iAns = vbOK Then
Cancel = True
Else ' if the user clicks Cancel erase the form altogether
Me.Undo
Cancel = True
End If
End If
End Sub
 
Back
Top