field calculation and comparison

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!
 
J

John Vinson

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.

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top