Transcript VB Data Validation

VB Data Validation
Unit 4
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.
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.
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.)
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.
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.
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.
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
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
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
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.
Mask Validation
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.
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
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.
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
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.
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
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.
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.
Independent Content
ValidationMin/Max Field Lengths

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
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.
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.
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
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.
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.
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
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.
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
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.)