Field validation when creating new record

[AusDBGuy]

Hi All,
I have a form which records payments for a membership. The form also displays the total amount paid by summing all the payments and the balance owing by subtracting the total paid from 50 (50 -txtTotalPaid).

The AmntPaid field defaults to $50.

I need to check if the AmntPaid is more than balance owing.

I'm already using the BeforeUpdate event which works great if the user is editing an existing record however if the default of $50 is left there obviously no update occurs and the BeforeUpdate event is not triggered.

I have tried using the validation in the field properties which does nothing.
I have tried the lostfocus event however for some reason trying to set focus back to the AmntPaid field is ignored completely and the focus shifts to the next control.
This is the code I'm using on the BeforeUpdate and is what I want to do if the default is left at $50.00.

If Me.mpPayAmnt > Me.txtBalance Then
    
    MsgBox "You can't pay more than is owing.", vbExclamation, "Over Paid"
    Cancel = True
    
    'Select all the text in mpPayAmnt
    mpPayAmnt.SelStart = 0
    mpPayAmnt.SelLength = Len(mpPayAmnt)
Else
    Me.Dirty = False
            
End If

Anyone have any ideas?

Thanks

 

[ypma]

I am using ac2003 . Not sure how you are checking the balance,
via a button or call ?
I have tried the following call using the txtbalance DbCLick to activate it and it seems to work ,I am a user not a pro so its just my thoughts .

Private Sub Balance()
If Me.MpPayAmnt > Me.txtBalance Then
MsgBox " You can't pay more than is owing.", vbExclamation, "Over Paid"
Cancel = True
Me.MpPayAmnt.SetFocus
'Me.MpPayAmnt.SelStart = 0
'Me.MpPayAmnt.SelLength = Len(mpPayAmpt)
If Me.Dirty = False Then
Me.Dirty = True
End If
End If
End Sub

Private Sub txtBalance_DblClick(Cancel As Integer)
Call Balance
End Sub

 

[ypma]

double reply ignore