Validating two fields

[Adam]

Hi All,

I have a user input form which, from two fields, works out the % share
between the two fields.


These two calculated fields should always equal 100%.


If there a way where I can add a validation criteria to the "Add New"
button so that it checks that these two calculated fields are equal to
100% before it goes to Add New record?


Many Thanks


Adam

 

[Allen Browne]

Adam, use the BeforeUpdate event of the *form* to perform this validation.
Access always fires this event before the record is allowed to be saved.

Due to the way fractional numbers work, they may not add up to exactly 100%,
so test if the difference is bigger than (say) 1/100th of a percent:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Abs(1 - Me.[Field1] - Me.[Field2]) > 0.0001 Then
Cancel = True
MsgBox "Doesn't add up."
End If
End Sub

By the way, an even easier solution might be to use the AfterUpdate event of
both test boxes to set the other one to 1 - the first one.

But then why are you storing both? If the rule is that they *must* add up to
100%, then storing the 2nd one is redundant. You could calcualte it as a
query field, and never have to worry about keepting it up to date.