validation

[slimjen]

All using ms access 2003. I have a input form in one of my database that has a calculated field to balance two other fields on the form. How can I prevent the user from going to the next record or exiting when the field does not balance? Ex. Total charges=$300 Invoice Total = $297.00. Balance=$3.00

=sum([totalcharges]-[invoicetotal])

Balance should always = $0.
The users should not be able to precede if the balance is not $0.
Thanks

[NoellaG]

Hi,

you can write a test in the beforeupdate event of the form.

greeting
NG

[slimjen]

? how?. something like if isnot[balance] = <>0
Just giving a shot in the dark. I have no idea how to write this.

[NoellaG]

Hi,

try something according the following style:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate

    If a <> b Then
        Cancel = True
        Me.Undo
        MsgBox "a must be equel to 2"
    End If

Exit_Form_BeforeUpdate
    Exit Sub
    
Err_Form_BeforeUpdate:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_Form_BeforeUpdate

End Sub

greetings
NG

[slimjen]

I tried this code. It's not working. I am not getting a msg before or after moving to the next record or exiting the form. What am I doing wrong. Thanks

 
Private Sub BALANCE_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_BALANCE_BeforeUpdate
    If Me.BALANCE <> 0 Then
        Cancel = True
        Me.Undo
        MsgBox "Balance must be equal to 0"
    End If
Exit_BALANCE_BeforeUpdate:
    Exit Sub
    
Err_BALANCE_BeforeUpdate:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_BALANCE_BeforeUpdate
End Sub

[NoellaG]

Hi,

it's the place you have put the code: you have put it in the before-update event of the control "BALANCE", you should put it in the beforeupdate event of the form itself

greetings
NG

[slimjen]

Hi, I am putting this in the balance before update of a subform. I also tried it on the main form and it still doesnt work. I need to put a save button on the form anyway; should I try on the save button to code?
Thanks