Need help to clear a form field

[vaidehikumar]

I have this code:

Private Sub Job_Number_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("[Job Number]", "Job", "[Job Number]= " & Me.[Job Number] & " ")) Then
MsgBox "Job Number doesn't exist Enter a job number that already exist."
Me.[Job Number].Undo
Cancel = True
End If
End Sub

It is not clearing the Job Number Field, and also it is not letting me to close the form without entering the Job number that already exist. If I try to close the form without entering the job number it gives me run time error " syntax error (missing operator) query expression '[Job Number] = '."

 

[Pat Hartman]

Move the code to the Form's BeforeUpdate event. You can't detect null values by having your validation code in the field itself because the code won't run unless the field is actually changed.

I prefer to not undo a user's changes without asking. It is sometimes helpful for him to see what he typed that was incorrect so I would remove the undo. I would add a setfocus though.
Me.[Job Number].SetFocus

 

[vaidehikumar]

I am sorry I do not understand when you say move the code to BeforeUpdate event. I thought I have the code within BeforeUpdate event. Thank you for your assistance.

 

[Pat Hartman]

Move the code to the Form's BeforeUpdate event