CODEWITHSUNDEEP
databasems-access
4GetFullOf
4GetFullOf

Reputation: 1738

IsNull ComboBox

I am trying to check if the user has completed all fields in my form. The following code works for all fields but im having issues with my multi select combo boxes. Now if I leave the comboBox blank the following code works perfectly and sends the bg color to red. But if I actually select something well then I get a mismatch error at the following line..If IsNull(ctrl) Or Len(ctrl) = 0 Then

Private Sub AddEmployee_Click()
    If CheckForEmpty = False Then
        MsgBox "Please fill in red boxes"
    Else

    End If
End Sub

Function CheckForEmpty() As Boolean
    CheckForEmpty = True
    ClearControlFormatting

    Dim ctrl As Control
    For Each ctrl In Me.Controls
        If ctrl.Tag = "FILL" Then
            If IsNull(ctrl) Or Len(ctrl) = 0 Then
                ctrl.BackColor = vbRed
                CheckForEmpty = False
            End If
        End If
    Next
End Function

Sub ClearControlFormatting()
    Dim ctrl As Control
    For Each ctrl In Me.Controls
        If ctrl.Tag = "FILL" Then
            ctrl.BackColor = vbWhite
        End If
    Next    
End Sub

Private Sub Form_Current()
    ClearControlFormatting    
End Sub

Upvotes: 0

Views: 69

Answers (2)

Erik A
Erik A

Reputation: 32632

The value of a multi-valued combo box is an array, hence the type mismatch.

Use IsArray to test if it's set:

Function CheckForEmpty() As Boolean
    CheckForEmpty = True
    ClearControlFormatting

    Dim ctrl As Control
    For Each ctrl In Me.Controls
        If ctrl.Tag = "FILL" Then
            If Not IsArray(ctrl) Then
                If IsNull(ctrl) OR Len(ctrl.Value) = 0 Then
                    ctrl.BackColor = vbRed
                    CheckForEmpty = False         
                End If
            End If
        End If
    Next
End Function

Upvotes: 2

Jericho Johnson
Jericho Johnson

Reputation: 759

Try the following:

Dim ctrl As Control
For Each ctrl In Me.Controls
    If ctrl.Tag = "FILL" Then
        If Len(ctrl.Value & "") = 0 Then
            ctrl.BackColor = vbRed
            CheckForEmpty = False
        End If
    End If
Next ctrl

When you concatenate a zero length string "" to a value in VBA, it will implicitly convert that value to a string, at which point you can safely check the Length of the string to determine if the user has entered/selected a value.

This type of comparison saves a step in your validation, and correctly handles when you are trying to check the Len() of a Null value. Len(Null) = Null NOT zero, therefore your current test has the possibility of a runtime logic error.

If the control is "empty" on the screen, depending on how it is bound to a field, it could evaluate as a zero length string, or a Null, causing different results in your Len() test.

Upvotes: 0

Related Questions