Prevent a blank or null value in a combo box on a form

[ritco]

How to prevent a blank or null value in a combo box on a form?

I have an unbound form that has a combo box (cboLotNum)with a OnNotInList event. The box gets populated from a lookup query if a record exists. Edits are allowed so the user can type a new lot number (if one doesn’t exist) or change an existing one. The OnNotInList will fire and handle add new or updating existing. This part works fine.

The issue is the OnNotInList only works if the user types something in the field. If the field is blank, the user can tab right through it.
I want the program to force the user to enter a value in that field.

The ultimate goal of the user is to print a report based on the values on the form so I added the following to the Print command button (btnPrint) and this works fine:

Private Sub btnPrintCert_Click()

Dim lotNum As String
lotNum = Me.cboLotNum.Value

If lotNum = Null Or lotNum = "" Then
MsgBox "Lot number cannot be blank. Please enter a valid lot number."
Me.cboLotNum.SetFocus
Else
DoCmd.OpenReport "rptCertCompView", acViewPreview, , , acWindowNormal
End If
End Sub

What I want to know is it possible to catch this before the user clicks the print button?

I tried the following on the combo box’s Before Update and On Lost Focus events.

If Me.cboLotNum.Value = Null Or Me.cboLotNum.Text = "" Then
MsgBox "Lot number cannot be blank. Please enter a valid lot number."
Me.cboLotNum.SetFocus
End If

The Before Update does nothing and the On Lost Focus works as far as giving the message but then goes to the next field. I can’t get it to focus back on itself.
I also tried setting the box’s Validation Rule to Is not Null and <>”” in which neither worked.
No error, just let me tab right through.

Do I need to just keep this in the Click event of the print button or can I force the issue during data entry?

 

[VilaRestal]

You can't compare something to Null. Null is not a value.

So the line:

If Me.cboLotNum.Value = Null Or Me.cboLotNum.Text = "" Then

should be

If IsNull(Me.cboLotNum.Value) Or Me.cboLotNum.Text = "" Then

or better yet

If Nz(Me.cboLotNum.Text,"") = "" Then

You can put the code in the combobox's Exit event. That fires when focus tries to move from the control and can be cancelled - keeping the focus there:

Private Sub cboLotNum_Exit(Cancel As Integer)
    If Nz(Me.cboLotNum.Text,"") = "" Then
        MsgBox "Lot number cannot be blank. Please enter a valid lot number."
        Cancel = True
    End If
End Sub

 

[ritco]

Thanks so much!!! That worked perfectly!
Just had to add my missing "Cancel = -1" to keep the focus.
I also changed my message box to OKCancel so the user can cancel and start over with a different item.
Here's the full code that works perfectly for this situation for anyone else that might encounter this:

Private Sub cboLotNum_Exit(Cancel As Integer)

Dim myMsg As Integer

If Nz(Me.cboLotNum.Text, "") = "" Then

myMsg = MsgBox("Lot number cannot be blank. Please enter a valid lot number.", vbOKCancel)

If myMsg = 1 Then
Cancel = -1
Else
Me.cboItem.SetFocus
End If
End If

End Sub