Cancel=True Not working

[AceBK]

I am trying to make only one field required, but only for specific customers. So in the customers table, I made a check box to determine if this field (RefNo) will be required for this specific customer. In the Orders form, I have included the check box from the customers table so that when I put an order in, I know if the RefNo should be required or not. So there are two requirements, if the checkbox is true and if the RefNo field is null, then I want a message box to say "Reference Number Required", but after I hit OK, the cursor goes to the next field, I want it to stay in the field. I have the Cancel=True function in the code, but this seems to only work when I use BeforeUpdate, which doesn't help, because if the user tabs over this field, it won't stop and say, Reference number required, therefore it will be missed. I put the following code in LostFocus, but the Cancel=true doesn't work now.

Private Sub RefNo_LostFocus()
If JobrefNo = -1 And IsNull(RefNo) Then
MsgBox "You must fill in a Job Reference Number", vbExclamation + vbOKOnly, "Job Reference Number!"
Cancel = True
End If
RefNo.SetFocus
End Sub

 

[pbaldy]

I'd use the before update event of the form. The user could use the mouse and skip that textbox entirely.

 

[AceBK]

Wow, so simple, Thanks

 

[gemma-the-husky]

having said that, this code should give you a compile error, as cancel is not an argument in the sub's header. you ought to add option explicit at the top of all your modules.

Private Sub RefNo_LostFocus()
If JobrefNo = -1 And IsNull(RefNo) Then
MsgBox "You must fill in a Job Reference Number", vbExclamation + vbOKOnly, "Job Reference Number!"
Cancel = True
End If
RefNo.SetFocus
End Sub

 

[pbaldy]

More info on Dave's suggestion:

http://www.baldyweb.com/OptionExplicit.htm