Need help to clear a Form Field

[vkumar]

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] = '."
Thank you!

[June7]

If you use a combobox, entering a non-existent job number could be prevented and this code totally unnecessary.

I haven't had much luck with the Undo method. Maybe should Undo the form, not just the textbox http://office.microsoft.com/en-us/ac...080753121.aspx

Or try just setting the textbox to null: Me.[Job Number] = Null

[vkumar]

Job table has lots of job number and it changes. It is not static. Users add job number everyday. That's why I do not want the combo box. If I have limiited job numbers and doesn't change I would use the combo box.
Me. Undo works, but it clears everything else in the form. I tried Null, I still have the same problem. The way I have set up now, the user must enter the correct job number, otherwise they can't close or claer the form. I don't like this, for example if the user enters a new invoice for a new job number, the user must have entered the Job number and other info in the Job table. If the user forgot to enter this before coming to the Invoice form, the user must enter a Job number that already exist and then close the form or clear the form and close it to go back and enter new the job info in the job table.
Thank you again for your replies

[June7]

Still not understanding resistance to combobox. I expect new job numbers would be created. As stated earlier, the combobox can deal with new numbers while also assisting user with selection of existing job number.

Your code doesn't allow the entry of a new job number, it only tells user the entered number doesn't exist and they must enter an existing number. How does that accommodate the situation of "Users add job number everyday."?

[Subwind]

Combo box is what you want, with a timer requery to update new jobs on the fly.

Set the form source to be the jobs record and have the combo box set to find the data afterupdate.

Much easier than the code you have.

~Matt