validating a calculabe field

[Guest]

Is there a way I can put a rule or something on field that is populated by
the sum of 3 other fields to display an error if a max value is calculated?

 

[Guest]

Assuming your current calculation is something like:

[field1]+[field2]+[field3]

then try somthing like this:

IIF([field1]+[field2]+[field3]>100,"Error",[field1]+[field2]+[field3])

where 100 is you max value

HTH

Rico

 

[Guest]

Yes, but where you put it depends on what behaviour you want. The problem is
that the After Update event does not fire if a control is changed via VBA.
It will not fire until you move to another record. So, there are two
possibilities:
1. Put the validation code in the Before Update event of the Form. This
will create the warning before the record is saved. It the example below, it
sets the focus to the first of the 3 fields used in the calculation.

2. If you want to know that the max has been exceeded during data entry,
then you will need to put this code in a function in the General section of
your form module and call it in the After Update event of all 3 of the
controls used in the calculation.

In the Form Before Update version:
If Me.CalclulatedField > MaxValue Then
MsgBox "Max Value Exceeded"
Cancel = True
Me.CalcField1.SetFocus
End If

The Function version:
Function CheckForMax() As Boolean
CheckForMax = Me.CalculatedField <= MaxValue
End Function
(Will return True if under or = max and False if over max)

To Call it:

If CheckForMax Then
MsgBox "Max Value Exceeded"
End If