Capture a null value error in a combobox

[Jeff H]

I'm doing a review of my db to see where users might get into trouble.

When entering visits they have to select a patient and a volunteer from dropdowns. The combos are initially empty, but if they select one and then inadvertently delete it, the error is: "You tried to assign a Null value to a variable that isn't a Variant data type."

They can correct it by selecting another item from the list, but if they don't do that then it leads to continual errors that I can only stop by crashing out of Access.

I want to catch that error, provide my own instructions, and return the combo to it's previous value (or reset it to nothing and clear the first and last names that it automatically populates). I've tried to catch it with BeforeUpdate and Change, but the Access error kicks in before either of them. I found this online, but it didn't work either:

Code: Select all

Private Sub cmbPID_Exit(Cancel As Integer)
If Nz(Me.cmbPID.Text, "") = "" Then
    MsgBox "This field cannot be empty. Either select a patient or clear the form."
    Cancel = True
End If
End Sub

[HansV]

How about

Code: Select all

Private Sub cmbPID_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.cmbPID) Then
        MsgBox "This field cannot be empty. Either select a patient or clear the form."
        Me.cmbPID.Undo
        ' Optional - leave the focus on the comb box
        Cancel = True
    End If
End Sub

If this doesn't work, do you have code in the On Error event of the form as a whole?

[Jeff H]

Yes sir! Looks like that will do it. I'm able to identify the error (DataErr = 3162), Undo the form's data, and substitute my own message. I had not previously noticed the Form_Error event. Thanks!

[Jeff H]

In case anyone else is interested, I was able to tweak the solution to undo just the deletion in the combobox instead of the whole form:

Code: Select all

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Const conNull = 3162 'Null in non-variant variable
Dim strMsg As String
Dim ctrl As Control

If DataErr = conNull Then
    Set ctrl = Screen.ActiveControl
    ctrl.Undo
    Response = acDataErrContinue
    strMsg = "Once selected, the Patient and Volunter " & _
        "IDs cannot be deleted." & vbCrLf & vbCrLf & _
        "However, a different selection can be made."
    MsgBox strMsg
End If
    
End Sub

[HansV]

Thanks for sharing!