Check Entry – Common Validations

Today’s post is about a routine I call Check_For_Normal_Entry_Errors.  In my opinion, the toughest part of coding is checking for entry errors because just knowing what good values look like isn’t good enough.  You also need to anticipate anything a user might do even though it may make no sense for anyone to do whatever that might be.  If you’ve been coding end user entry applications for any length of time, you know exactly what I mean.  I’m not going to tell you that I’ve solved that riddle, but I do offer some basic validation routines that fit many common entry problems.  I also offer the source code so you can fill in the gaps I have left.

Check_For_Normal_Entry_Errors is meant to be a function that is never modified for a specific entry problem.  It is for ‘general’ entry problems and for that reason is placed in modTableUpdate.  Check_For_Normal_Entry_Errors works in concert with Check_Entry which is a routine meant to be modified for a particular worksheet’s specific validation needs.  That routine may have to be modified often; but, not everytime.  In the example we are building, we won’t need to modify it.  Check_For_Normal_Entry_Errors handles everything we need.

Check_For_Normal_Entry_Errors receives a cell and finds which validation rule should  be applied to it from the Fields Definition table discussed in several prior posts.  The rules that can be specified in the Fields Definition table that Check_For_Normal_Entry_Errors can apply to the cell are to make sure:

• Required entries are not empty
• The ACD column only contains the values “A”, “C”, “D”, or “X” (Add, Change, Delete or eXisting)
• Date fields contain valid dates (NOTE: This does not check to make sure dates are future or past, workdays or not, etc.)
• Flag fields contain “Y” or “N” (Yes or No)
• Numeric fields contain numbers only
• Code fields are limited to values in an Excel Code Table
• Type Code Fields are limited to values that match a “Type” value in the entry and a “Code” value in an Excel Type Table
• Text fields are limited to a designated number of characters
• Custom Edits/Validations are handled by the worksheet’s Check_Entry routine

There are a few support routines here that I have not yet covered.  They are small routines that mark cells in error or as having passed our validation rules.  Those will be the subject of our next post.

One last note, this routine will never, and should never try to correct anything.  ALL this routine does is determine if an entry is valid or not.  Now that may seem odd because I spent so much time on frmSelect which is all about helping users correct bad entries.   I included frmSelect because it is called by routines Check_For_Normal_Entry_Errors uses, but Check_For_Normal_Entry_Errors tells those routines to NOT use frmSelect for that purpose.  Those routines serve multiple masters and when they serve Set_Entry_Defaults, frmSelect will come into play.  Just keep in mind that there are times when you want to let the user know their entry is wrong and help them fix it, and times when ALL you want to know is if the entry is ready for posting.  Those functions have a lot of overlap, but they MUST be independent of each other.

As always, I believe the code’s documentation explains how the routines works so I’ll not duplicate what’s in the code.  Here is the code. 

Function Check_For_Normal_Entry_Errors( _
		sWorksheet As String, sFields As String, lField As Long, _
                  rngCell As Range, rngMsg As Range) As Boolean

'   Description:Checks a cell against the most common validation rules.
'               Place special rules in Worksheet's Check_Entry routine

'   Parameters: sWorksheet  Worksheet containing data AND Check_Entry routine
'               sFields     Range name containing field descriptions
'               lField      Relative row (-1) within sFields of field to check
'               rngCell     Cell being checked
'               rngMsg      Cell in which to put any error messages
                           
'   Example:    bResult = Check_For_Normal_Entry_Errors( _
                              Me.Name, sDetailFields, _
                              lCol - lColData + 1, _
                              Cells(lRow, lCol), _
                              Cells(lRow, lColNote))

'     Date   Init Modification
'   01/12/06 CWH  Initial Programming

    On Error GoTo ErrHandler           
    Check_For_Normal_Entry_Errors = Success     'Assume the best
       
    Dim s As String     'Generic String Variable
    Dim t As String     'Type for a code
    Dim v As Variant    'Generic Variant Result
      
    lField = lField + 1

    With Range(sFields)
   
        If UCase(.Cells(lField, lColReq)) = "Y" And Trim(rngCell) <= "" Then
            Cell_Error rngCell, rngMsg, .Cells(lField, lColHdg) & _
                " is required"
            Check_For_Normal_Entry_Errors = Failure
       
        ElseIf Trim(rngCell) > "" Then
       
            Select Case UCase(.Cells(lField, lColVTyp))
           
               'eXisting, Added, Changed, Deleted record indicator
                Case "ACD"
                    If InStr(1, "ACDX", rngCell) <> 0 Then
                        Cell_Checked rngCell
                    Else
                        Cell_Error rngCell, rngMsg, .Cells(lField, lColHdg) & _
                            "In ACD: A=Add, C=Change or D=Delete"
                        Check_For_Normal_Entry_Errors = Failure
                    End If
               
               'Dates
                Case Is = "DATE"
                    If IsDate(rngCell) Then
                        Cell_Checked rngCell
                    Else
                        Cell_Error rngCell, rngMsg, .Cells(lField, lColHdg) & _
                            " must be a valid date and time"
                        Check_For_Normal_Entry_Errors = Failure
                    End If
                   
               'Yes or No flags
                Case Is = "YORN"
                    If InStr(1, "YyNn", rngCell) <> 0 Then
                        Cell_Checked rngCell
                    Else
                        Cell_Error rngCell, rngMsg, .Cells(lField, lColHdg) & _
                            " must be Y (Yes) or N (No)"
                        Check_For_Normal_Entry_Errors = Failure
                    End If
               
               'Numeric Values
                Case Is = "#", "$", "NUMBER"
                    If IsNumeric(rngCell) Then
                        Cell_Checked rngCell
                    Else
                        Cell_Error rngCell, rngMsg, .Cells(lField, lColHdg) & _
                            " must be numeric"
                        Check_For_Normal_Entry_Errors = Failure
                    End If
               
               'Custom Edits/Validation rules
                Case Is = "CUST"                   
                    s = .Cells(lField, lColVTbl)    'Get rule Name
                    v = Worksheets(sWorksheet).Cust_Edit(s, rngCell, False)
                    If Not IsNull(v) Then
                        Parse_SQL_Result CStr(v), rngCell.Row, rngCell.Column
                        Cell_Checked rngCell
                    Else
                        Cell_Error rngCell, rngMsg, .Cells(lField, lColHdg) & _
                            " is not valid"
                        Check_For_Normal_Entry_Errors = Failure
                    End If
               
               'Excel Code Tables
                Case Is = "XLC"
                    s = .Cells(lField, lColVTbl)    'Get XL Table Name
                    v = XL_Lookup(Range(s), "Code", "Code", rngCell.Text)
                    If Not IsNull(v) Then
                        Cell_Checked rngCell
                    Else
                        Cell_Error rngCell, rngMsg, .Cells(lField, lColHdg) & _
                            " is not valid"
                        Check_For_Normal_Entry_Errors = Failure
                    End If
               
               'Excel Type Tables
                Case Is = "XLT"
                   'Excel Table holding Types and Codes

                    s = .Cells(lField, lColVTbl)       
                   'The Type value within the Entry to restrict codes by                    
                    t = Cells(rngCell.Row, Fields_Field_Column(sFields, s))
                    s = Trim(Left(s, InStr(1, s, "{") - 1))                
                    v = XL_Lookup(Range(s), "Code", "Code", _
                                  rngCell.Text, "Type", t)
                    If Not IsNull(v) Then
                        Cell_Checked rngCell
                    Else
                        Cell_Error rngCell, rngMsg, _
                            .Cells(lField, lColHdg) & _
                            " is not valid for code " & rngCell.Cells(1, 0)
                        Check_For_Normal_Entry_Errors = Failure
                    End If
                               
               'Limits on the number of characters in a text field
                Case Else
                    v = Val(.Cells(lField, lColVTyp))
                    If v > 1 Then
                        If Len(rngCell) <= v Then
                            Cell_Checked rngCell
                        Else
                            Cell_Error rngCell, rngMsg, _
                                .Cells(lField, lColHdg) & _
                                " must be " & v & "characters or less"
                            Check_For_Normal_Entry_Errors = Failure
                        End If
                    End If
                   
            End Select
        End If
    End With

ErrHandler:
   
    If Err.Number <> 0 Then
        Check_For_Normal_Entry_Errors = Failure
        Cell_Error rngCell, rngMsg, rngCell.Cells(lField, lColHdg) & _
             Err.Description
        MsgBox _
            "Check_For_Normal_Entry_Errors - Error#" & Err.Number & vbCrLf _
             & Err.Description, vbCritical, "Error", Err.HelpFile, _
             Err.HelpContext
        End If
    On Error GoTo 0

End Function