Validation Rule for Adding Percentages

C

Christine

My form has a bunch of different characteristics that the user is required to
enter a weight for. Not weight as in pounds but as in importance so one is
weighted at 25% and the other at 8% to make one so much more important than
the other

Each of the text boxes where the user enters percentages are named
txtweight1 .... txtweight8 and formated as percentages.

I am trying to in the box below have the message ALLOCATION OKAY appear if
all the percentages add to 100% and CHECK ALLOCATIONS appear if they don't
add to 100% I also want them to not be able to submit the form unless the
allocation is okay.

I can do this in Excel:
=IF(SUM(D4:D11)=100%,"ALLOCATION OK","CHK ALLOCATION")
but they want the model in Access not Excel and I can't seem to figure out
where to start writing the VBA code for this. Thanks!
 
K

Klatuu

In Access, the check needs to be in the Before Update event of the form.

If txtWeight1 + txtWeight2 + txtWeight3 + txtWeight4 + txtWeight5 +
txtWeight6 + txtWeight7 + txtWeight8 = 100% Then
MsgBox "ALLOCATION OK"
Else
MsgBox "CHK ALLOCATION"
Cancel = True
End If

The Cancel = True line prevents the record with incorrect data from being
updated.
 
Top