Validation of combo with a 0 value (1 Viewer)

oxicottin

Learning by pecking away....
Local time
Today, 07:24
Joined
Jun 26, 2007
Messages
850
Hello, I use this validation I found years ago by Dirk Goldgar and cant figure something out. I have 3 combo boxes with a default value of 0 which the validation skips over because there is a value there. Where in the code can I have it say there is no value if there is a 0 as well as if it were null? Thanks!

Code:
Function fncRequiredFieldsMissing(frm As Form) As Boolean

' Check form (passed as <frm> for controls marked as  "Required"
' in their Tag properties.  If any required control is empty, return
' True, display an error message listing all the "missing" fields,
' and set the focus to the first missing control (where "first" is
' based on the controls' tab order).  In the list of missing controls,
' use each control's caption if possible, else use the name of the
' control after stripping off the expected object-type prefix (if
' present).
'
' Copyright (c) 2013, Dirk Goldgar.  You may use this code freely in your
'       applications, provided that the copyright notice remains intact.


    On Error Resume Next

    Dim ctl As Access.Control
    Dim strErrCtlName As String
    Dim strErrorMessage As String
    Dim strMsgName As String
    Dim lngErrCtlTabIndex As Long
    Dim blnNoValue As Boolean

    lngErrCtlTabIndex = 99999999  'more than max #controls

    For Each ctl In frm.Controls
        With ctl
            Select Case .ControlType
            Case acTextBox, acComboBox, acListBox, acCheckBox
                If .Tag = "Required" Then
                    blnNoValue = False
                    If IsNull(.value) Then
                        blnNoValue = True
                    Else
                        If .ControlType = acTextBox Then
                            If Len(.value) = 0 Then
                                blnNoValue = True
                            End If
                        End If
                    End If

                    If blnNoValue Then

                        strMsgName = vbNullString
                        If .Controls.Count = 1 Then
                            strMsgName = .Controls(0).Caption
                            If Right$(strMsgName, 1) = ":" Then
                                strMsgName = Trim$(Left$(strMsgName, Len(strMsgName) - 1))
                            End If
                        End If
                        If Len(strMsgName) = 0 Then
                            strMsgName = .Name
                            Select Case Left$(strMsgName, 3)
                            Case "txt", "cbo", "lst", "chk"
                                strMsgName = Mid(strMsgName, 4)
                            End Select
                        End If

                        strErrorMessage = strErrorMessage & vbCr & _
                                        "   " & strMsgName

                        If .TabIndex < lngErrCtlTabIndex Then
                            strErrCtlName = .Name
                            lngErrCtlTabIndex = .TabIndex
                        End If

                    End If
                End If
            Case Else
                ' Ignore this control
            End Select
        End With
    Next ctl


    If Len(strErrorMessage) > 0 Then
        MsgBox "The following fields are required:" & vbCr & _
               strErrorMessage, _
               vbInformation, "Required Fields Are Missing"
        frm.Controls(strErrCtlName).SetFocus
        fncRequiredFieldsMissing = True
    Else
        fncRequiredFieldsMissing = False
    End If
End Function
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:24
Joined
Feb 19, 2013
Messages
16,522
another alternative

if nz(.value,0)=0 then
 

Users who are viewing this thread

Top Bottom