Error checking

[Me.Howard]

In a form I am adding a new record to TblRequirement


The form data entry property is set to yes
In a text box I enter a job number
In a second text box I do a LookUp from a different table to populate
This works well as long as the job number is valid
I am checking for valid job number with the following code.

Private Sub Job_Click()
If IsNull(Job) Or Job < 1000 Or Job > 9999 Then
MsgBox "Invalid Job Number - Reenter"
DoCmd.Close
DoCmd.OpenForm "FrmRequirementAdd"
End If
End Sub

This does not catch the null or the < or the >
What am I doing wrong

Thanks

[June7]

Could use a multi-column combobox to offer valid job numbers and set LimitToList property to yes. http://datapigtechnologies.com/flash...combobox3.html

Then instead of DLookup, expression in textbox can reference column of combobox.

No VBA needed. But, if you want to add a job number not in the combobox list, use VBA in the combobox NotInList event. https://www.accessforums.net/access/...ate-55139.html

Is Job a text or number type field?

[Me.Howard]

Job is a number field.
Why cant I check the validity as shown in the code in the original post?

[June7]

I am not sure. Looks like it should.

If you want to provide db for analysis, follow instructions at bottom of my post.

Did you consider the combobox suggestion?

[Missinglinq]

Why would you put this code in the OnClick event of the Textbox you're validating? With Data EntryProperty set to Yes you can only enter New Records, and the Job field will always be Null when you click into the Textbox!

Linq ;0)>

[June7]

Doh! Good catch, Linq!