VB Data Validation

1. VB Data Validation Unit 4

2. Input Validation • Validating user input before it is written can improve the quality of the data stored. Good validation schemes can also make your program user friendly and, in many cases, can increase the speed at which users can enter valid data. • We cover several specific topics on input validation, including the following: • Field-level validation versus form-level validation • How to speed data entry by filtering keyboard input • How to use input masks to give users hints when entering data • How to limit user choices and speed input with validation lists • How to handle required field inputs in Windows forms • How to handle conditional field input validation in Windows forms • After you learn how to develop input validation routines, you build a set of validation custom controls. These custom controls handle seven valuable validation routines that you can use in your projects throughout the guide. You can also use these custom controls in any project you build in the future.

3. Input Validation • Input validation is the process of checking the data entered by the user before that data is saved to the database. Input validation is a proactive process--it happens while data is being entered. • Input validation can be used to give users guides on how to enter valid data. The best example of this kind of input validation is the use of a validation list. A validation list is a list of valid inputs for a field. If the user has only a limited number of possible valid choices for an input field, there is much less chance of a data entry error occurring. Good validation schemes give the user a list of valid input from which to choose while performing data entry. • Input validation can automatically edit data as the user enters it, instead of telling the user to fix invalid entries. For example, if the data entered in a field must be in all capital letters, the program should automatically convert lowercase characters to uppercase instead of waiting while the user enters mixed case, and then reporting an error and forcing the user to re-enter the data. • Input validation reaches beyond the individual keystroke and field. It is also important to validate data at the form level. Input validation schemes should make sure that all required fields on a form are completed properly. If you have several fields that must be filled with valid data before the record can be saved to the database, you must have a method for checking those fields before you allow the user to attempt to save the record.

4. Input Validation • Almost every field in your database requires some type of input validation. Before you design your form, put together a list of all the fields you need on the form and answer the following questions for each input field: • Must data be entered in the field? (Is it a required field?) • What characters are valid/invalid for this field? (Numeric input only, capital letters only, no spaces allowed, and so on.) • For numeric fields, is there a high/low range limit? (Must be greater than zero and less than 1000, can't be less than 100, and so on.) • Is there a list of valid values for this field? (Can user enter only Retail, Wholesale, or Other; Name must already be in the Customer table, and so on.) • Is this a conditional field? (If users enter Yes in field A, they must enter something in field C.)

5. Input Validation • Even though each data entry form is unique, you can use some general guidelines when putting together input validation schemes. • If possible, limit keystrokes to valid values only. For example, if the field must be numeric, don't allow the user to enter character values. If spaces are not allowed, make sure the space bar is disabled. Help the user by limiting the kinds of data that can be entered into the field. • Limit input choices with lists. If there is a limited set of valid inputs for a field, give the user a pick list or set of radio buttons to choose from. • Inform the user of range limits. If a field has a high or low range limit, tell the user what the limits are. • Point out required fields on a form. Mark required fields with a leading asterisk (*) or some other appropriate character. Possibly change the background color of required fields. • Group conditional fields together on the form. If entering Yes in one field means that several other fields must be completed, put the additional fields close to the Yes/No field to help the user. Keep conditional fields of this type disabled until the Yes/No flag has been set. This helps the user see that new fields must be entered.

6. field level • The first level of validation is at the field level. This is the place where you can make sure the user is entering the right characters in the field, entering the data into the field in the proper format, and entering a valid value based on a list of possible choices.

7. Filtering Keyboard Input • One of the easiest ways to perform input validation is to filter keyboard input. • Filtering keyboard input requires capturing the keystrokes of the user before they appear on the screen and filtering out the keystrokes you do not want to appear in the input controls. • You can filter invalid or undesirable keystrokes by creating a beep for the user each time an invalid key is pressed (for example, a beep each time a letter is pressed in a numeric field). • You can also convert the invalid key to a valid one (for example, change lower case to upper case). Or you can simply ignore the keystroke completely and prevent the invalid values from ever appearing in the input control.

8. Filtering Keyboard Input • Private Sub txtNumber_KeyPress(KeyAscii As Integer)    `    Dim strValid As String    `    strValid = "0123456789"    `    If InStr(strValid, Chr(KeyAscii)) = 0 Then        KeyAscii = 0    End If    `End Sub

9. The KeyPress event to force letters to uppercase. • Private Sub txtUpper_KeyPress(KeyAscii As Integer)    `    KeyAscii = Asc(UCase(Chr(KeyAscii))) ` change to uppercase    `End Sub

10. A single KeyPress event to check for valid entry and force uppercase. Private Sub txtCombined_KeyPress(KeyAscii As Integer) ` Dim strValid As String ` strValid = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ" ` KeyAscii = Asc(UCase(Chr(KeyAscii))) ` If KeyAscii > 26 Then If InStr(strValid, Chr(KeyAscii)) = 0 Then KeyAscii = 0 End If End If ` End Sub

11. Input Masking • It is very common to have fields on your form that require special input formats. • Examples of special formats would be telephone numbers, Social Security numbers, hour/minute time entry, and so on. • Visual Basic 5 ships with a bound data control that handles special input and display formatting--the MaskedEdit control. The MaskedEdit control works like the standard Visual Basic 5 textbox control, with a few added properties that make it a powerful tool for your input validation arsenal. • Let's add a phone number input field to the form. • Add a new label to the form and set its caption property to Phone. • Now add a MaskedEdit control to the form. • Set its Name property to mskPhone, the Mask property to (###) ###-#### and the PromptInclude property to False.

12. Mask Validation

13. Validation Lists • One of the most common field-level input validation routines is the use of a validation list. The list contains a set of possible inputs for the field--usually displayed in a list box or a drop-down list control. Instead of having to guess at a valid value, the user can simply scan the list and click on the proper choice. • Validation lists require a bit more programming to use, but the rewards far exceed the effort. Using validation lists virtually guarantees that you will not have a data entry error occur on the input field. • Before you can use a validation list for input validation, you must first have a list. It is usually a good idea to load any validation lists you need for a form at the time you load the form. This means that validation lists should be loaded in the Form_Load event. Let's add some code to your project that loads a drop-down list box with a list of possible customer types. • First add another label and a drop-down combo box control to the form. Set the label caption to Customer Type, and set the drop-down combo box Name property to cboCustType and the Style property to 2--DropDown List.

14. The Form_Load event to load a list box. Sub Form_Load () ` ` load dropdown list box cboCustType.AddItem "Retail" cboCustType.AddItem "Wholesale" cboCustType.AddItem "Distributor" cboCustType.AddItem "Other" End Sub

15. Validation Lists • Notice that when you first start the form, the combo box shows an empty value. This indicates no selection has been made. • You can add some code to your form that selects a default item from the list, too. Add the following line of code to the Form_Load event: cboCustType.ListIndex = 0 ` set default value • Now when the form starts, you see the first value in the list has already been selected.

16. Form-Level Validation • Form-level validation is an essential part of designing a good validation scheme for your form. Although many input errors can be caught and corrected at the field level, there are several validation steps that can only be performed well at the form level. • Although field-level validation is performed at the time a key is pressed or at the time a field loses focus, form-level validation is performed at the time the user presses Enter, or clicks the OK or Save button. • These are validations that are done after the user has entered all data, but before any attempt is made to store the values to a data table. • Form-level validation can be divided into three groups: • Independent content validation • Required field validation • Dependent field validation

17. Independent Content ValidationHigh/Low Ranges • A common form-level validation routine is one that checks the upper and lower values of a numeric entry and makes sure the value is within the high/low range. • This is very useful on all types of forms that have dollar amounts or unit count minimum and maximum values.

18. Private Sub cmdOK_Click()    `    Dim intHigh As Integer    Dim intLow As Integer    Dim strHighLowMsg As String    `    intHigh = 100    intLow = 1    strHighLowMsg = "High/Low field must contain a value between " & _       CStr(intLow) & " and " & CStr(intHigh)    `    If Val(txtHighLow) < intLow Or Val(txtHighLow) > intHigh Then        MsgBox strHighLowMsg        txtHighLow.SetFocus    End If    `End Sub

19. The code establishes the integer variables for the high and low in the range, sets them to 100 and 1 respectively, and then checks the value entered into the txtHighLow text control. If the value is out of the allowed range, a message is displayed, and the input cursor is moved back to the field that contains the invalid data. Notice that the message not only tells the user that the data is invalid, it also tells the user what values are acceptable. If the data entered is within range, the program exits normally.

20. Independent Content ValidationMin/Max Field Lengths • Another common form-level validation step is to make sure that character strings meet the minimum or maximum length requirements. This is done in the same way numeric values are checked for high and low ranges. • Let's add input validation to ensure that the Uppercase textbox you placed on the form earlier is no longer than 10 characters, and at least 3 characters in length. You just need to add the code to the cmdOK_click event that checks the txtUpper field for length.

21. Independent Content ValidationMin/Max Field Lengths In the code, you added variables for the minimum and maximum length of the entry field, a new message variable, and a flag variable to show that all validation steps passed. Notice that you changed the structure of the validation steps from a simple If_Then_Else to a series of If_Then routines. If the validation does not pass, a flag is set to make sure the form does not unload. • Private Sub cmdOK_Click()    `    Dim intHigh As Integer    Dim intLow As Integer    Dim strHighLowMsg As String    `    Dim intMinLen As Integer    Dim intMaxLen As Integer    Dim strMinMaxMsg As String    `    Dim blnOK As Boolean    `    intHigh = 100    intLow = 1    strHighLowMsg = "High/Low field must contain a value between " & _       CStr(intLow) & " and " & CStr(intHigh)    `    intMinLen = 3    intMaxLen = 10    strMinMaxMsg = "Upper field must be between " & _       CStr(intMinLen) & " and " & CStr(intMaxLen) & " long."    `    blnOK = False    `    ` check high/low field    If Val(txtHighLow) < intLow Or Val(txtHighLow) > intHigh Then        MsgBox strHighLowMsg        blnOK = False        txtHighLow.SetFocus    End If    `    ` check upper field    If Len(txtUpper) < intMinLen Or Len(txtUpper) > intMaxLen Then        MsgBox strMinMaxMsg        blnOK = False        txtUpper.SetFocus    End If    `    ` set if all passed    If blnOK = True Then        Unload Me    End If    `End Sub

22. Required Fields • Almost every form has at least one field that is required input. • Some forms may have several. • Checking for required input fields is done at the form level. • Let's add code at the cmdOK_click event that makes sure that users fill out the Combined field every time. • All you need to do is validate that the txtCombined field contains valid data.

23. The only change you made is to check the length of the string in the txtCombined textbox. If the result is zero, an error message is displayed. Notice the use of the Trim function to remove any trailing or leading spaces from the txtCombined string. This makes sure that users who enter blank spaces into the field do not get past the validation step. Private Sub cmdOK_Click()    `    Dim intHigh As Integer    Dim intLow As Integer    Dim strHighLowMsg As String    `    Dim intMinLen As Integer    Dim intMaxLen As Integer    Dim strMinMaxMsg As String    `    Dim blnOK As Boolean    `    intHigh = 100    intLow = 1    strHighLowMsg = "High/Low field must contain a value between " & _       CStr(intLow) & " and " & CStr(intHigh)    `    intMinLen = 3    intMaxLen = 10    strMinMaxMsg = "Upper field must be between " & _       CStr(intMinLen) & " and " & CStr(intMaxLen) & " long."    `    blnOK = False    `    ` check high/low field    If Val(txtHighLow) < intLow Or Val(txtHighLow) > intHigh Then        MsgBox strHighLowMsg        blnOK = False        txtHighLow.SetFocus    End If    `    ` check upper field    If Len(txtUpper) < intMinLen Or Len(txtUpper) > intMaxLen Then        MsgBox strMinMaxMsg        blnOK = False        txtUpper.SetFocus    End If    `    ` check the combined field    If Len(Trim(txtCombined)) = 0 Then        MsgBox "Combined field is a required field"        blnOK = False        txtCombined.SetFocus    End If    `    ` set if all passed    If blnOK = True Then        Unload Me    End If    `End Sub

24. Conditional Fields • There are times when entering a value in one field of the form means that other fields on the form must also contain valid data. Fields of this type are called conditional fields. • A good example of conditional field validation can be found in an order tracking system. For example, when a user enters Yes in the Ship to Site? field, he or she must then enter a valid value in the Shipping Address field. The Shipping Address field is a conditional field because its validation is based on the condition of the Ship to Site? field. • Now add a conditional validation to the project. Make the field CustType conditional to the field Upper. In other words, if the Upper field contains data, the CustType field must contain data.

25. Now you must enter valid data in both fields before the form unloads. You have probably also found out that each time you click the OK button, all the form-level validation steps are performed. It is good programming practice to deliver all the validation results to the user at once. It can be very frustrating to fill out a form, receive an error message, and then fix the message, only to receive another one, and another one, and so on. Private Sub cmdOK_Click()    `    Dim intHigh As Integer    Dim intLow As Integer    Dim strHighLowMsg As String    `    Dim intMinLen As Integer    Dim intMaxLen As Integer    Dim strMinMaxMsg As String    `    Dim blnOK As Boolean    `    intHigh = 100    intLow = 1    strHighLowMsg = "High/Low field must contain a value between " & _       CStr(intLow) & " and " & CStr(intHigh)    `    intMinLen = 3    intMaxLen = 10    strMinMaxMsg = "Upper field must be between " & _       CStr(intMinLen) & " and " & CStr(intMaxLen) & " long."    `    blnOK = False    `    ` check high/low field    If Val(txtHighLow) < intLow Or Val(txtHighLow) > intHigh Then        MsgBox strHighLowMsg        blnOK = False        txtHighLow.SetFocus    End If    `    ` check upper field    If Len(txtUpper) < intMinLen Or Len(txtUpper) > intMaxLen Then        MsgBox strMinMaxMsg        blnOK = False        txtUpper.SetFocus    End If    `    ` check the combined field    If Len(Trim(txtCombined)) = 0 Then        MsgBox "Combined field is a required field"        blnOK = False        txtCombined.SetFocus    End If    `    ` check conditoinal upper/custtype fields    If Len(Trim(txtUpper)) <> 0 And Len(Trim(cboCustType)) = 0 Then        MsgBox "If Upper field conains data then " & _          "the Customer Type field must also contain data"        blnOK = False        cboCustType.SetFocus    End If    `    ` set if all passed    If blnOK = True Then        Unload Me    End If    `End Sub

26. Summary • Today you learned how to perform input validation on data entry forms. You learned that input validation tasks can be divided into three areas: • Key filtering: Preventing unwanted keyboard input • Field-level validation: Validating input for each field • Form-level Validation: Validating input across several fields

27. Summary • You also learned that you should ask yourself a few basic questions when you are developing validation rules for your form. • Is it a required field? • What characters are valid/invalid for this field? (Numeric input only, capital letters only, no spaces allowed, and so on.) • For numeric fields, is there a high/low range limit? (Must be greater than zero and less than 1000, can't be less than 100, and so on.) • Is there a list of valid values for this field? (Can the user enter only Retail, Wholesale, or Other; Name must already be in the Customer table, and so on.) • Is this a conditional field? (If users enter Yes in field A, then they must enter something in field C.)