Advanced Visual Basic Techniques
(Publisher: John Wiley & Sons, Inc.)
Author(s): Rod Stephens
ISBN: 0471188816
Publication Date: 06/01/97

ExpenseReporter’s last function used to validate monetary fields is ValidMoney. This function determines whether the new text contains a numeric value with at most two digits after the decimal point. Because the application cannot tell whether the user has finished typing at this point, the routine cannot require that the field contain exactly two digits. In other words, the function must accept the values “12.34,” “12.3,” “12.,” and “12.” Allowing prefixes of valid strings is also important for date fields where “1/1/97,” “1/1/,” and even “1” must all be allowed initially.

Public Function ValidateMoney(ctl As TextBox, msg As String) As Boolean
Dim txt As String
Dim bad As Boolean
Dim pos As Integer

    ‘ Assume the field is valid.
    bad = False

    txt = ctl.Text
    If txt = “” Then
    ‘ Allow “”.
    ElseIf Not IsNumeric(txt) Then
        bad = True
    Else
        pos = InStr(txt, “.”)
        If pos = 0 Then
            ‘ Not found. Add “.00”
            ctl.Text = ctl.Text & “.00”
        ElseIf pos < Len(txt) - 2 Then
            ‘ Too many digits after the decimal.
            bad = True
        ElseIf pos = Len(txt) - 1 Then
            ctl.Text = ctl.Text & “0”
        ElseIf pos = Len(txt) Then
            ctl.Text = ctl.Text & “00”
        End If
    End If

    If bad Then _
        PresentMessage ctl, msg, vbOKOnly, _
            “Invalid Monetary Format”

    ValidateMoney = bad
End Function

Module Fields.BAS contains other functions for validating fields that allow only letters, digits, and dates.

Note that a program can set the value of a text field while bypassing keystroke validations. Field validations will not prevent an application from setting a numeric text field’s value to “ABC.” This lapse can sometimes cause problems. When the input focus reaches such a field, nothing the user can type will make the input valid. Even placing the input focus at the end of the field and pressing the Backspace key will not work because “AB” is also invalid. The only way the user can fix the value is to select it all and delete it with the Backspace or Delete key. To prevent this sort of confusion, an application should take care not to place invalid values in validated fields.

Validating Forms

Some validations cannot be performed as the user is typing into fields. An application may require that one field have a larger value than another. Another field may be required but only if a second is not blank. These sorts of constraints require form-level validations.

Deciding when to perform form validation can be difficult. Generally, the application should validate a form before another application might look at it—that usually means just before the form is saved in a database or file. In some applications the user may want to save a form that is not fully filled in and finish later. In that case, validating the form before it is saved may inconvenience the user.

The final output of the ExpenseReporter application is a printed form, so this program validates the form before printing or print previewing. It also performs some field formatting tasks when it validates the form and when it loads data from a file. For example, if a dollar amount does not include two digits after the decimal point, the application adds them. The value “12” becomes “12.00.”

ExpenseReporter’s ValidForm function performs form validations. ValidForm uses the BlankRequired function to verify that the user has entered values in all required fields. It uses functions ValidateDigits, ValidateDate, and ValidateMoney to verify that the data values have correct formats. Date field validations must allow the user to type values such as “1/1/” and “1,” but those values are not allowed at form validation time.

ValidForm also computes the total meal and miscellaneous expenses for each day. If any total exceeds the per diem of $50.00, the program notifies the user and concludes the form is invalid.

Function ValidForm() As Boolean
Const PER_DIEM = 50     ‘ Max meals plus misc per day.

Dim i As Integer
Dim date_text As String
Dim total As Single

    ‘ If we fail we leave early.
    ValidForm = False

    If BlankRequired(NameText, _
        “Please enter the employee name.”) _
        Then Exit Function

    If BlankRequired(IDText, _
        “Please enter the employee ID.”) _
        Then Exit Function
    If ValidateDigits(IDText, _
        “The employee ID must contain only digits 0 - 9.”) _
        Then Exit Function

    If BlankRequired(DeptText, _
        “Please enter the department number.”) _
        Then Exit Function
    If ValidateDigits(DeptText, _
        “The department number must contain only digits 0 - 9.”) _
        Then Exit Function

    If BlankRequired(ProjText, _
        “Please enter the project number.”) _
        Then Exit Function
    If ValidateDigits(ProjText, _
        “The project number must contain only digits 0 - 9.”) _
        Then Exit Function

    If BlankRequired(FromDateText, _
        “Please enter the trip start date.”) _
        Then Exit Function
    If ValidateDate(FromDateText, _
        “The trip start date does not have a valid date format.”) _
        Then Exit Function

    If BlankRequired(ToDateText, _
        “Please enter the trip end date.”) _
        Then Exit Function
    If ValidateDate(ToDateText, _
        “The trip end date does not have a valid date format.”) _
        Then Exit Function

    ‘ Validate the nonblank rows of data.
    For i = 0 To MaxRow
        If RowNotBlank(i) Then
            If BlankRequired(DateText(i), _
                “Date is required for non-blank expense rows.”) _
                Then Exit Function
            If ValidateDate(DateText(i), _
                “This row’s date does not have a valid date format.”) _
                Then Exit Function

            If BlankRequired(LocationText(i), _
                “Location is required for non-blank expense rows.”) _
                Then Exit Function

            If BlankRequired(CategoryCombo(i), _
                “Category is required for non-blank expense rows.”) _
                Then Exit Function

            If BlankRequired(DescrCombo(i), _
                “A description is required for non-blank expense rows.”) _
                Then Exit Function

            If BlankRequired(AmountText(i), _
                “An amount is required for non-blank expense rows.”) _
                Then Exit Function
            If ValidateMoney(AmountText(i), _
                “Please enter a valid monetary value for the prepaid amount.”) _
                Then Exit Function
        End If
    Next i

    ‘ Make sure prepaid and advance are filled in.
    If BlankRequired(PrepaidText, _
        “Please enter the prepaid amount.”) _
        Then Exit Function
    If ValidateMoney(PrepaidText, _
        “Please enter a valid monetary value for the prepaid amount.”) _
        Then Exit Function

    If BlankRequired(AdvanceText, _
        “Please enter the advance amount.”) _
        Then Exit Function
    If ValidateMoney(AdvanceText, _
        “Please enter a valid monetary value for the advance amount.”) _
        Then Exit Function

    ‘ Sort the rows by date.
    SortRows

    ‘ Check that meal and misc totals for each day
    ‘ do not exceed the per diem.
    date_text = “”
    total = 0
    For i = 0 To MaxRow
        If RowNotBlank(i) Then
       ‘ See if this is a new day.
            If DateText(i) <> date_text Then
                If total > PER_DIEM Then
                    Beep
                    MsgBox “Total meals plus miscellaneous expenses on ” & _
                        date_text & “ is $” & _
                        Format$(total, “0.00”) & _
                        “. This exceeds the allowed per diem of $” & _
                        Format$(PER_DIEM, “0.00”) & “.”
                    Exit Function
                End If

                date_text = DateText(i)
                total = 0
            End If

            If CategoryCombo(i).Text = “Meal” Or _
               CategoryCombo(i).Text = “Misc” _
                Then _
                    total = total + CSng(AmountText(i).Text)
        End If
    Next i

    If total > PER_DIEM Then
        Beep
        MsgBox “Total meals plus miscellaneous expenses on ” & _
            date_text & “ is $” & _
            Format$(total, “0.00”) & _
            “. This exceeds the allowed per diem of $” & _
            Format$(PER_DIEM, “0.00”) & “.”
        Exit Function
    End If

    ValidForm = True
End Function