[RESOLVED] Access 2003 VBA BeforeUpdate Event

[Hack]

I need to know how Cancel is supposed to work in a BeforeUpdate event. From everything I have read, it is supposed to cancel the update if it is set to True.

I have a series of textboxes on a form. All of these textboxes are bound to a field in a table.

There is a textbox at the bottom with its control source set to add up all of the other textboxes. All of the textboxes contain a percentage number and the totals textbox is setup to display the overall percent. That textbox cannot exceed 100%

In the BeforeUpdate event of all the individual textboxes I have this code

 Private Sub TextBox1_BeforeUpdate(Cancel As Integer)
If Val(txtTotal) > 1 Then
   Cancel = True
   MsgBox “Changing this value will cause the total to exceed 100%”, vbOKOnly + vbExclamation, "Can Not Exceed 100%"
   End If
End Sub

When I change a total in one of the textboxes, and totals textbox obediently adds every up immediately and tells me what I have. I figured that changing a total in a textbox, which caused the overall total to exceed 100%, would cause Cancel to be True, and therefore, since I’m using the BeforeUpdate event, that change would not be written back to the table.

Well, goofy me…I do get the message box telling me I have gone over 100%, but the number I have entered causing this overage does get written back to the database table.

I have two questions:
1. Isn’t BeforeUpdate supposed to fire BEFORE the update takes place?
2. How do I actually cancel an update if said update causes the overall total to exceed 100%?

[Hack]

I resolved this myself...I discovered a property of the textbox called "OldValue" - this is perfect for this situation. If the total exceeds 100% I pop up a messagebox box and the revert whatever was input to the original (or OldValue) of the textbox.