(as published in Spectrum
magazine Jul/Aug 1997)

updated May 27,
1997

Validating field data

I have a series of text boxes on a form. The
first one is called PartNum and the second one is called
OrderNum. I want to be able to validate that the part number is
on file, and if not, then to display "Item X is not
valid" and allow entry of the part number again. So in the
PartNum_LostFocus event, if the part is invalid, I display the
error message and then do a PartNum.SetFocus to return to that
field.

What is happening, is that the error message
"Item X is not valid" is displayed, but then it is
executing OrderNum_LostFocus before it is going back to
PartNum_GotFocus. Why does the OrderNum_LostFocus code get run? I
am baffled! - J. Smith, Western Design Howden

I hear this a lot. You put some validation code
in a control's LostFocus event that does a SetFocus back to that
control if there is an error, and yet some other control's
GotFocus or LostFocus still gets run. Here's what's happening:

Imagine that 'focus' is tennis ball that is
either in one hand or the other. You write code for
'leaving-left-hand' (LeftHand_LostFocus). When you transfer
the ball to your right hand, that code gets run. Also, a
transfer to your right hand will queue up an event for any
'ball-arrived-in-right-hand' code (RightHand_GotFocus).

If there is an error, the
'leaving-left-hand' code says "put ball back in left
hand" (LeftHand.SetFocus). But where is the ball? It's
in your right hand, which means that in order for it to
return to your left hand, it has to leave your right hand.
That will cause any 'leaving-right-hand'
(RightHand_LostFocus) code to be run! What if a different
error causes that code to say "put ball in right
hand"? Now you have a problem. Such dilemmas can easily
cause unpredictable behavior (or worse, unstoppable looping).

So how do you solve the problem? There is no
one answer, since every situation calls for a different approach.
The real question should be: "How do you validate entered
data in Visual Basic?" I will devote the remainder of this
article to that issue.

The following guidelines apply to how and when
to detect field validation errors. I won't get into how to handle
the errors - that would be a separate topic entirely, (although
you might want to read "About Face" by Alan Cooper for
some fascinating views on error handling). I have divided the
most common data validation tasks into four groups:

1. Field format validation (e.g.: date or
numeric)

2. Lookup field validation (e.g.: part
number)

3. Multiple field consistency validation
(e.g.: state matches zip)

4. Mandatory field validation (e.g.:
address required)

Field format validation

This type of validation usually only involves a
single field. To begin with, you should attempt to prevent an
error in the first place. In other words, if it is a
numerics-only field, use the KeyPress event to only accept digits
(and the backspace key). For date fields, you should make every
attempt to accept any recognizable date. As MultiValue
programmers, we used to take the entered value, ICONV it (using a
"D" conversion), and check if it was null. In Visual
Basic, you simply apply the IsDate() function. You can often
safely use the LostFocus event for this kind of checking. Don't
use the Change event - it is invoked for every keystroke and you
never know when the user is finished entering their value.

Lookup field validation

Many fields are keys to records in your
database. As above, your first task is to try and prevent an
invalid key from being entered. If your keys contain only upper
case letters, convert letters to upper case as they are entered.
Better yet, try using a drop-down combo box or searching your
database as the user types (see previous Expert article). If
this is not possible, then once again you will probably want to
use the LostFocus event to verify that the key exists. You may
want to put in logic to ensure that the LostFocus only performs
this validation if the contents of the field have changed -
there's no point in re-reading the data if the user is just
stepping through the field.

Multiple field consistency validation

There are times when the contents of two or
more fields must match according to some rule. It is not
advisable to perform this kind of validation in the LostFocus
event. There are two reasons for this: (1) the fields should be
enterable in either order  you have no way of knowing that
the user has finished with one and is intending to change the
other; and (2) it is not clear which field you should send the
user to in order to correct the problem. Data validation
involving multiple fields is usually best done when the user is
saving the entire window (e.g. clicking OK). At that point, they
have indicated that they are happy with all the window contents,
and it is appropriate to validate the data against the
consistency rules.

Mandatory field validation

A window that has one or more
mandatory fields (i.e. fields that must have non-null
data) are also quite common. There are a number of ways to avoid
this error arising at all. For example, if the user must select
from one of six choices for a field, you can replace it with six
option buttons and set the Value property of the most likely
choice to True. It is not possible for the user to set all
options to False once one of them is true.

Another strategy for enforcing mandatory fields
is to set the windows OK or Save buttons Enabled
property to False until all mandatory fields are filled in. While
this is a common technique, a disadvantage of this approach is
that it may be difficult for the user to determine exactly what
must be done in order to enable the completion button. Since you
cant detect them clicking on a disabled button, you
cant give them a message saying whats missing.

In summary, data validation is a major part of
what programmers do, and we have only skimmed the surface here,
but I hope you picked up a few good ideas.