Day 6

Input Validation

Today you'll learn about one of the most important aspects of database programming—input validation. Validating user input before it is written to the database can improve the quality of the data stored in your tables. Good validation schemes can also make your program appear user friendly and, in many cases, can increase the speed at which users can enter valid data.

We'll cover several specific topics on input validation including the following:

After you learn how to develop input validation routines, you will build a validation library. This library will have seven valuable routines that you can use in your projects throughout the book. Since these routine are designed to be reuseable, you will be able to use them in any project you build in the future.

Also, today is the day you build the main data entry form for the CompanyMaster database you created earlier in the week. You'll use all the techniques you've learned this week, including the use of bound data controls and input validation to build a solid data entry form.

Before you get into the details of how to perform input validation, let's first talk about what input validation is and why it is so important to good database application design.

What is 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 pro-active process—it happens while data is being entered. Input validation is not the same thing as error trapping. Error trapping is a reactive process—it happens after the data has been entered. This is an important point. Input validation should be used to prevent errors. If you have good input validation schemes, you'll have fewer errors to trap! You'll learn more about the reactive process on Day 14, "Error Trapping."

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 entry for a field must be all in capital letters, the program should automatically convert lowercase characters to upper case instead of waiting until the user enters mixed case, then reporting an error and forcing the user to reenter 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.

Conditional input fields must be validated, too. A conditional field is slightly different from a required field. Usually conditional fields occur when a user has checked a Yes/No box and then must enter additional data to complete the process. For example, if the user indicates on a form that the customer requests all products to be shipped instead of picked-up, input validation should make sure that valid data has been entered into the shipping address fields. Another example of conditional field validation is when entering a value in one field requires that the value in another field be within a certain range. For example, if the customer's credit limit is above $50,000, then you must enter a valid credit-worthiness code of 5 or above. In this case, the two fields must be checked against one another and verified before the user can save the record to the database.

As you can see from the preceding examples, input validation is more than just making sure the data entered in a field is correct. Input validation should be viewed as a set of rules that ensure quality data is entered into the system. Before you begin writing your data entry forms, you should spend time developing a comprehensive set of validation rules. Once you develop these rules, you will be ready to start creating your data entry form.

Common Input Validation Rules

Almost every field in your database will require some type of input validation. Before you design your form, put together a list of all the fields you will need on the form and answer the following questions for each input field:

Even though each data entry form is unique, you can use some general guidelines when putting together input validation schemes.

Field Level Validation

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.

For the rest of this section, you'll be building a sample application that illustrates the various input validation methods we will cover. If you haven't done so already, start up Visual Basic 4 and create a new project. Set the caption of the form to Input Validation, save the form as INPVAL.FRM, and save the project as INPVAL.VBP.

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 (beep each time a letter is pressed in a numeric field). You can also convert the invalid key to a valid one (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.


TIP:

Keep in mind that not all your potential users may be able to hear an audible beep and could become confused at the inability to input data. Windows 95 has several useful Accessibility Options that you may want to review, including the use of message boxes for the hearing-impaired.


For the first keyboard filtering example, you will build an input control that only accepts numerals zero through nine. First, add a label control and a text box control to the form. Set the caption property of the label control to Numbers. Set the Name property of the text box control to txtNumber and set the text property to blank. Your form should resemble the one in Figure 6.1.

Figure 6.

Adding the Numbers input controls.

Save and run the program. You can enter any type of data in the text box that you wish—numbers, letters, spaces, and so on. Now you'll add a small bit of code that will filter out all but the numerals zero through nine. You'll do this by using the text box control KeyPress event.

The KeyPress event occurs each time a user presses a key while the field has the focus. Each time a key is pressed while the cursor is in the text box control, the ASCII value of the key is sent to the KeyPress event where you can evaluate it and act accordingly.


NOTE:

Each key on the keyboard has a ASCII (American Standard Code for Information Interchange) numeric value. Your Visual Basic 4 documentation has a list of the ASCII codes for each key on the keyboard.


In this example, you want to ignore any keystroke that is not a 0,1,2,3,4,5,6,7,8, or 9. To do this, you need to add a small bit of code (Listing 6.1) to the KeyPress event of the txtNumbers text box.

Listing 6.1. Limiting data entry in the Keypress event.





Sub txtNumbers_KeyPress (KeyAscii As Integer)



   Dim cValid As String



   '



   cValid = "0123456789"



   '



   If InStr(cValid, Chr(KeyAscii)) = 0 Then



      KeyAscii = 0



   End If



End Sub

In Listing 6.1, you declared a string variable that holds the list of valid keys. The next line loads the string with the valid keys for this field, and the next line checks to see if the key pressed is in the string of valid keys. It does this by converting the numeric value passed by Visual Basic 4 in the KeyAscii parameter (the ASCII value of the key pressed) into a readable character using the Visual Basic 4 Chr function and searching for the result in the list of valid keys in the cValid string. If the key pressed is not in the cValid string, the keystroke is set to zero. Setting the keystroke to zero is telling Visual Basic 4 to pretend nothing was ever typed!

Now save and run the program. No matter what keys you type, only the numerals zero through nine appear in the text box. You have filtered out all but numerals. You may also notice that keystrokes, such as the backspace and delete keys, no longer work! You've told Visual Basic 4 to ignore them. You can fix that by adding a statement that checks to see if the keystroke is a control code. Control codes are used in Visual Basic 4 to indicate that the key the user pressed was not a printable character put a keyboard control character. Common control characters are the Escape key, the Return Key, the Backspace, key, and so on.

You can also add any other characters to the validity list if you like. For example, you probably want to be able to enter a minus sign, a plus sign, and a decimal point in this number field. To do this, all you need to do is add those three characters to the cValid string. Your program code should now look like Listing 6.2.

Listing 6.2. The KeyPress event with control characters.





Sub txtNumbers_KeyPress (KeyAscii As Integer)



   Dim cValid As String



   '



   cValid = "0123456789+-."



   '



   If KeyAscii > 26 Then ' if it's not a control code



      If InStr(cValid, Chr(KeyAscii)) = 0 Then



         KeyAscii = 0



      End If



   End If



End Sub

Notice that in Listing 6.2, you have first tested to see if the key pressed was greater than 26. ASCII code 26 is the last Visual Basic 4 control code. The routine above now skips over filtering of control codes. When you save and run the program, you will be able to pass the plus, minus, and decimal point characters into the text box, too.

Now let's create validation code that only accepts uppercase characters. This will be a bit trickier. Instead of ignoring lowercase input, you'll convert it to uppercase, then pass it through to the text box.

First add another label and text box control. Set the label caption to Uppercase. Set the Name property of the text box to txtUpper and set the text property to blank. Your form should look like the one in Figure 6.2.

Figure 6.2

Adding the Uppercase control and conversion to the form.

The code needed for the txtUpper KeyPress event is in Listing 6.3. Notice that, if the keystroke is not an uppercase letter, instead of setting the Visual Basic 4 KeyAscii parameter to zero (discarding it), this routine converts it to an uppercase value by subtracting 32 from the ASCII value.

Listing 6.3. The KeyPress event to force letters to uppercase.





Sub txtUpper_KeyPress (KeyAscii As Integer)



   If Chr(KeyAscii) >= "a" And Chr(KeyAscii) <= "z" Then



      KeyAscii = KeyAscii - 32



   End If



End Sub

When you save and run the program you'll see that any letter key you enter converts to an uppercase letter and passes through to the text box.

The two types of keyboard filters illustrated here, (discard or convert) can be combined to form a powerful input validation tool. Let's create a validation example that allows only uppercase letters A through Z or numerals zero through nine—no spaces or any other characters.

First add a new label/text box control pair. Set the label caption property to Combined. Set the text box name property to txtCombined and the text property to blank. Refer to Figure 6.3 for positioning and sizing.

Figure 6.3

Adding the Combined control to the form.

Listing 6.4 shows how to combine a check against a valid list and a conversion of keystrokes into a single input validation.

Listing 6.4. A single KeyPress event to check for valid entry and force uppercase.





Sub txtCombined_KeyPress (KeyAscii As Integer)



   Dim cValid As String



   '



   cValid = "0123456789"



   '



   If KeyAscii > 26 Then ' it's not a control code



      If InStr(cValid, Chr(KeyAscii)) = 0 Then



         If Chr(KeyAscii) >= "a" And Chr(KeyAscii) <= "z" Then



            KeyAscii = KeyAscii - 32



         Else



            KeyAscii = 0



         End If



      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, US social security numbers, hour/minute time entry, and so on. Visual Basic 4 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 4 text box 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.


TIP:

It is essential that you set the PromptInclude property to False when using the MaskedEdit control as a bound control. If the PromptInclude property is set to True, you will get a database error each time you add a new record to the table or attempt to save or read a record that has a null value in the data field linked to the MaskedEdit bound control.


Your form should resemble Figure 6.4.

Figure 6.4

Adding the MaskedEdit Phone control to the form.

You do not need to add any additional filtering to the control since the MaskedEdit control makes sure that only digits are entered and that the input is limited to ten digits formatted as a standard US phone number.

Save and run the program. You can see that when the control is initialized, the phone number mask is displayed. When the MaskedEdit control receives the focus, a series of underlines appear as an input guide for the user. The underlines disappear when control is given to an object other than the MaskedEdit control.


NOTE:

The formatting characters of the MaskedEdit control are not saved to the database field when the PromptInclude property is set to False. This means that in the previous example, only the phone number digits would be saved to the data table, not the parentheses or the dash.


The Visual Basic 4 MaskedEdit control offers an extensive set of input masking tools. It ships with several input masks predefined, including dollar amounts, US phone numbers, and several date and time formats. To view these formats, select the MaskedEdit control, click the right (alternate) mouse button, and select Properties from the menu that appears. You will find the formats on the General tab.

You can also create custom input format masks for inventory part numbers, e-mail addresses, and so on. Although we won't cover all the possibilities here, there is one other MaskedEdit format option that you will illustrate on your form in this lesson because it is very useful when displaying dollar amounts.

The MaskedEdit control gives you the power to add a display mask in addition to an input mask. Up to this point, you have been using the input mask capabilities of the MaskedEdit control. Now let's add a control that shows the display capabilities, too.

Add another label control and another MaskedEdit control to the form. Set the label caption property to Dollars. Set the MaskedEdit control name property to mskDollars and the format property to $#,##0.00;($#,##0.00).


TIP:

The MaskedEdit display property actually has three parts, each separated by the semi-colon (;). Part one determines how positive values will be displayed. Part two determines how negative values will be displayed. Part three determines how zero values will be displayed.


This property affects the display of the data, not the input, so you will not see any input guides when you set the format property or when you save and run the program. Your form should look like the one in Figure 6.5.

Figure 6.5

Adding the Dollar control to the form.

Now run the program and enter a numeric value in the Dollars text box. When you leave the text box to go to another control, you'll see the MaskedEdit control format the display of the amount you entered. Your screen should resemble Figure 6.6. Please note that two decimal places will always appear to the right of the decimal place.

Figure 6.6

The display results of the MaskedEdit control.

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 control to the form. Set the label caption to CustType and set the drop-down combo box Name property to cboCustType and the Style property to 2—DropDown List. Your form should look like the one in Figure 6.7.

Figure 6.7

Adding the DropDown List control to the form.


NOTE:

You cannot change the height of the combo box control in Visual Basic 4. It is set at 300 twips and cannot be updated.


Now add Listing 6.5 to load the list box with valid values.

Listing 6.5. 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

In Listing 6.5, you are adding values directly to the list using program code. Each AddItem method adds an additional valid selection to the list. You could also load the control with values from a data table. This would give you a more dynamic list of valid values. For now, stick to the direct load example here; later in this book, you will add validation lists loaded from data tables.

Now save and run the program. You can now click on the down arrow of the drop-down list box and see the list of valid values. Now the user can't help but pick a correct item for the input field.

Up to this point, you have been developing methods for handling field level validation. The next step is to add validation routines at the form level.

Form Level Validation

Form level validation is an essential part of designing a good validation scheme for your form. While 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, clicks the OK or Save button. These are validations that are done after all fields have been entered by the user, but before any attempt is made to store the values to a data table.

Form level validation can be divided into three groups:

Now let's look at each type of form level validation.

Independent Content Validation: High/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.


NOTE:

Although it might seem that this kind of validation should be done at the field level, it is better to perform it at the form level. If a user enters a value that is not within the acceptable range, the field that contains the invalid data must be given focus so the user can correct the entry. Setting the control's focus is best done outside of any other control's GotFocus or LostFocus event. Also, since a user can use the mouse to skip over any field on the form, placing independent content validation routines within the controls' events means that users may skip important validation steps in the process.


To set up the form level validation, first add a single command button to the form. Set its Name property to cmdOK and its caption property to &OK. Now add another label/text box pair to the form. Set the label caption to High/Low. Set the text box Name property to txtHighLow and the text property to blank. Refer to Figure 6.8 for sizing and placement.

Figure 6.8

Adding the High/Low control and OK button to the form.

Next add Listing 6.6 to the cmdOK_click event.

Listing 6.6. The form level validation routine to check for values in a range.





Sub cmdOK_Click ()



   Dim nHigh As Integer



   Dim nLow As Integer



   '



   nHigh = 100



   nLow = 1



   '



   If Val(txtHighLow) < nLow Or Val(txtHighLow) > nHigh Then



      MsgBox "High/Low field must contain a value between " +



      Str(nLow) + " and " + Str(nHigh)



      txtHighLow.SetFocus



   Else



      Unload Me



   End If



End Sub

The code in Listing 6.6 establishes the integer variables for the high and low 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.

Now save and run the program. If you skip to the OK button without entering data or enter data outside the allowed range, you'll see the validation message.

Independent Content Validation: Min/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 the Uppercase text box 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 in Listing 6.7 to the cmdOK_click event that checks the txtUpper field for length.

Listing 6.7. The form level validation routine to check the length of fields and a valid range of values.





Sub cmdOK_Click ()



   Dim nHigh As Integer



   Dim nLow As Integer



   Dim nMinLen As Integer



   Dim nMaxLen As Integer



   Dim nOK As Integer



   '



   nHigh = 100



   nLow = 1



   nMinLen = 3



   nMaxLen = 10



   nOK = True



   '



   ' check highlow field



   If Val(txtHighLow) < nLow Or Val(txtHighLow) > nHigh Then



      MsgBox "High/Low field must contain a value between " +



       Str(nLow) + " and " + Str(nHigh)



      nOK = False



      txtHighLow.SetFocus



   End If



   '



   ' check upper field



   If Len(txtUpper) < nMinLen Or Len(txtUpper) > nMaxLen Then



      MsgBox "Upper field must be between " + Str(nMinLen) + 



      " and " + Str(nMaxLen) + " long."



      nOK = False



      txtUpper.SetFocus



   End If



   '



   ' see if it's all ok



   If nOK = True Then



      Unload Me



   End If



End Sub

In Listing 6.7, you added variables for the minimum and maximum length of the entry field and flag variable to show that all validation steps passed. Notice 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.

Save and run the form to test the validation rule. You'll see that now, both form level validation rules must be met before the form will unload.


NOTE:

The txtUpper field has both field level and form level validation rules applied to it. The field level routine executes when data is entered into the field. The form level validation routine executes when this data record is saved. It is perfectly acceptable, and sometimes recommended, to have both field level and form level validation for the same control.


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. Listing 6.8 shows how this is done.

Listing 6.8. The form level validation routine to check for required fields.





Sub cmdOK_Click ()



   Dim nHigh As Integer



   Dim nLow As Integer



   Dim nMinLen As Integer



   Dim nMaxLen As Integer



   Dim nOK As Integer



   '



   nHigh = 100



   nLow = 1



   nMinLen = 3



   nMaxLen = 10



   nOK = True



   '



   ' check highlow field



   If Val(txtHighLow) < nLow Or Val(txtHighLow) > nHigh Then



      MsgBox "High/Low field must contain a value between " + 



      Str(nLow) + " and " + Str(nHigh)



      nOK = False



      txtHighLow.SetFocus



   End If



   '



   ' check upper field



   If Len(txtUpper) < nMinLen Or Len(txtUpper) > nMaxLen Then



      MsgBox "Upper field must be between " + Str(nMinLen) + " 



      and " + Str(nMaxLen) + " long."



      nOK = False



      txtUpper.SetFocus



   End If



   '



   ' check combined field



   If Len(Trim(txtCombined)) = 0 Then



      MsgBox "Combined field is a required field"



      nOK = False



      txtCombined.SetFocus



   End If



   '



   ' see if it's all ok



   If nOK = True Then



      Unload Me



   End If



End Sub

The only change you made is to check the length of the string in the txtCombined text box. 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 will 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 a conditional field validation can be found in an order tracking system. For example, when a user enters Yes in the Ship To Site? field, they then must 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.

Let's 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, then the CustType field must contain data. See Listing 6.9 for an example on how to do this.

Listing 6.9. The form level conditional validation routine.





Sub cmdOK_Click ()



   Dim nHigh As Integer



   Dim nLow As Integer



   Dim nMinLen As Integer



   Dim nMaxLen As Integer



   Dim nOK As Integer



   '



   nHigh = 100



   nLow = 1



   nMinLen = 3



   nMaxLen = 10



   nOK = True



   '



   ' check highlow field



   If Val(txtHighLow) < nLow Or Val(txtHighLow) > nHigh Then



      MsgBox "High/Low field must contain a value between " + 



      Str(nLow) + " and " + Str(nHigh)



      nOK = False



      txtHighLow.SetFocus



   End If



   '



   ' check upper field



   If Len(txtUpper) < nMinLen Or Len(txtUpper) > nMaxLen Then



      MsgBox "Upper field must be between " + Str(nMinLen) + 



      " and " + Str(nMaxLen) + " long."



      nOK = False



      txtUpper.SetFocus



   End If



   '



   ' check combined field



   If Len(Trim(txtCombined)) = 0 Then



      MsgBox "Combined field is a required field"



      nOK = False



      txtCombined.SetFocus



   End If



   '



   ' check conditional upper/custtype fields



   If Len(Trim(txtUpper)) <> 0 And Len(Trim(cboCustType)) = 0 Then



      MsgBox "If Upper field contains data then the 



      CustType field must contain data"



      nOK = False



      cboCustType.SetFocus



   End If



   '



   ' see if it's all ok



   If nOK = True Then



      Unload Me



   End If



End Sub

Save and run the program. Now you must enter valid data in both fields before the form will unload. 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.

Creating Generic Validation Routines

The input validation routines you have created today cover most of the situations you are likely to encounter when designing data entry forms. In fact, after you design one or two of these forms, you'll begin to see that you are writing the same validation code over and over again. Instead of repeatedly writing the some code, or even constantly performing cut, copy, and paste operations, you can modify these routines slightly and create a reusable set of validation routines that can be plugged into all your data entry programs.

Before you build the CompanyMaster data entry form you need to build an Input Validation Library module. This module can be used for the CompanyMaster and any other data entry form you design in the future.

Creating a Global .BAS Module

The first step in writing a reusable library routine is to open a Visual Basic code module—often called a .BAS module (pronounced bass—like the fish). This module will contain procedures and functions that can be called from any form or code module. To create a new .BAS module, select Insert | Module or click on the module button of the tool bar. This opens a code window with a default name of MODULEX.BAS, where X is a number starting at one (see Figure 6.9).

Figure 6.9

Opening a Visual Basic .BAS Module.

For now, save this empty module as LIBVALID.BAS. This is where you can build all your generic input validation routines.

Adding a New Function to the Library

The next step in building a library is adding a new function that can be called from any Visual Basic 4 program. Let's create a keyboard filter function that only allows numeric values to pass through to the underlying control.

The first thing you have to do is create the function header and footer. Use Insert | Procedure to create a new function called KeyNumbers (see Figure 6.10).

Figure 6.10

Creating a new function for the LIBVALID.BAS library.

Now, enter the code in Listing 6.10 to build the function. The code in the KeyNumbers routine is almost identical to the code you wrote earlier in this chapter for the txtNumbers field (see Listing 6.1).

There are a few things different from the original validation routine in Listing 6.1. First, the function now has a passed parameter—nKeyValue. This is the ASCII value of the key the user presses. Second, the last line of code sets the function return value. The rest of the code is unchanged.

Listing 6.10. The KeyNumbers field level validation function.





Function KeyNumbers (nKeyValue As Integer) As Integer



   Dim cValid As String



   '



   cValid = "0123456789+-."



   '



   If nKeyValue > 26 Then ' if it's not a control code



      If InStr(cValid, Chr(nKeyValue)) = 0 Then



         nKeyValue = 0



      End If



   End If



   KeyNumbers = nKeyValue



End Function

The last thing you need to do is replace the code in the txtNumbers_KeyPress event with a call to the new KeyNumbers function.





Sub txtNumbers_KeyPress (KeyAscii As Integer)



   KeyAscii = KeyNumbers(KeyAscii)



End Sub

The preceding code excerpt gets executed each time the user presses a key in the txtNumbers text box. Each time a key is pressed, a call is made to the KeyNumbers function. The KeyNumbers function filters out all keystrokes except control codes and numeric input. Save and run the program to test the KeyNumbers function.

The KeyUpper Library Function

Now add another validation function to the library that forces all letters to uppercase. Call the function KeyUpper. Listing 6.11 shows how the function should look.

Listing 6.11. The KeyUpper field level function to force uppercase letters.





Function KeyUpper (nKeyValue As Integer) As Integer



   '



   If Chr(nKeyValue) >= "a" And Chr(nKeyValue) <= "z" Then



      nKeyValue = nKeyValue - 32



   End If



   '



   KeyUpper = nKeyValue ' set the return value



End Function

The KeyUpper function takes a single ASCII key value as an input parameter and scans the value to see if it is a lowercase letter. If it is, the routine converts the value to an uppercase letter. Finally, KeyUpper returns the key value as an output parameter.

The following lines replace the code you wrote earlier (see Listing 6.3) in the txtUpper_KeyPress event.





Sub txtUpper_KeyPress (KeyAscii As Integer)



   KeyAscii = KeyUpper(KeyAscii)



End Sub

Save and run the program to test the new KeyUpper function.

The KeyUpperNumber Library Function

Listing 6.12 contains the library function to handle uppercase or numeric input. Create a new function KeyUpperNumber and enter the following code.

Listing 6.12. The KeyUpperNumber function to force uppercase letters and pass control codes.





Function KeyUpperNumber (nKeyValue As Integer) As Integer



   '



   ' passes uppercase letters



   ' converts lowercase to uppercase



   ' passes 0123456789 and control codes



   ' rejects all else



   '



   If nKeyValue > 26 Then



      If Chr(nKeyValue) >= "a" And Chr(nKeyValue) <= "z" Then



         nKeyValue = nKeyValue - 32



      End If



      '



      If Chr(nKeyValue) >= "A" And Chr(nKeyValue) <= "Z" Then



         nKeyValue = nKeyValue



      Else



         nKeyValue = KeyNumbers(nKeyValue)



      End If



   End If



   KeyUpperNumber = nKeyValue



End Function

The new function, KeyUpperNumber ignores control codes, converts lowercase letters to uppercase, and passes numeric characters. Replace the existing code (entered from Listing 6.4) in the txtCombined_KeyPress event with the following code. Save and run the program.





Sub txtCombined_KeyPress (KeyAscii As Integer)



   KeyAscii = KeyUpperNumber(KeyAscii)



End Sub

The InRange Library Function

Now let's add a function to handle range validation for numeric fields. Create a new function called InRange and enter the code in Listing 6.13.

Listing 6.13. The InRange function to handle validation of numeric ranges.





Function InRange (ctlName As Control, cFieldName As String, vHigh As Variant,



 vLow As Variant, nMsg As Integer) As Integer



   '



   ' ctlName     = name of control to check



   ' cFieldName  = name of field to display



   ' vHigh       = high end of range



   ' vLow        = low end of range



   ' nMsg        = flag to show message



   '



   If ctlName.Text < vLow Or ctlName.Text > vHigh Then



      If nMsg = True Then



         MsgBox cFieldName + " must be between " + Str(vLow) + 



         " and " + Str(vHigh), 0, "Validation Error"



      End If



      ctlName.SetFocus



      InRange = False



   Else



      InRange = True



   End If



End Function

A few things in the InRange function deserve attention. First, notice that the actual control is being passed to the function. This is done so that the function can invoke the SetFocus method. Notice also that not just the control name is passed—the form reference (Me) is passed also. This is required because the library functions have no idea what form is being used. You'll also see that all the numeric values are declared as Visual Basic 4 Variant type. This is done because you do not know if the numeric values passed to InRange will be Integer, Single, Double, or Long. The Visual Basic 4 Variant type will handle any type it is given.

Finally, you added a parameter to control the display of an error message. Most of the time you will want to display an informative message to the user after an error is caught. However, there are times when you would rather not display an error. For example, when you want to test a single field for a combination of validations—for example, it must be within a range, and it is a dependent field—you may want to test for both, get the result, and display a single custom message instead two messages.

Listing 6.14 shows how InRange is called. Place this in the cmdOK_Click event, and then save and run the project.

Listing 6.14. Calling the InRange function from the cmdOK_Click event.





Sub cmdOK_Click ()



   Dim nMinLen As Integer



   Dim nMaxLen As Integer



   Dim nOK As Integer



   '



   nMinLen = 3



   nMaxLen = 10



   nOK = True



   '



   ' check highlow field



   nOK = InRange(Me.txtHighLow, "HighLow", 100, 1,True)



   '



   ' check upper field



   If Len(txtUpper) < nMinLen Or Len(txtUpper) > nMaxLen Then



      MsgBox "Upper field must be between " + Str(nMinLen) + " 



      and " + Str(nMaxLen) + " long."



      nOK = False



      txtUpper.SetFocus



   End If



   '



   ' check combined field



   If Len(Trim(txtCombined)) = 0 Then



      MsgBox "Combined field is a required field"



      nOK = False



      txtCombined.SetFocus



   End If



   '



   ' check conditional upper/custtype fields



   If Len(Trim(txtUpper)) <> 0 And Len(Trim(cboCustType)) = 0 Then



      MsgBox "If Upper field contains data then the 



      CustType field must contain data"



nOK = False



      cboCustType.SetFocus



   End If



   '



   ' see if it's all ok



   If nOK = True Then



      Unload Me



   End If



End Sub

The following code was inserted into the cmdOK_Click event to call the InRange function:





nOK = InRange(Me.txtHighLow, "HighLow", 100, 1,True)

The first parameter passed, Me.txtHighLow, identifies the control to check. The Me is used in front of the control name to denote the current form. This is used, rather than the form name, to identify the currently active form in case there are multiple instances of the same form open.

The second parameter, HighLow, is the string that is passed to the error statement in case values entered into the field do not meet the specified range set by the vHigh (100) and vLow (1) parameters. Finally, the nMsg value is set to True to tell the InRange function to display a message box if an error occurs.

The CheckSize Library Function

The next function to add to the library is called CheckSize. This function compares the actual length of a data entry string and compares it to a minimum and maximum length. If either limit is exceeded, a message is displayed and the function returns False. If all is okay, the function simply returns True. Listing 6.15 contains the code for the CheckSize function.

Listing 6.15. The CheckSize function to check field length.





Function CheckSize (ctlName As Control, cFieldName As String, nMinLen As



 Integer, nMaxLen As Integer, nMsg As Integer) As Integer



   Dim nLen As Integer



   '



   ' ctlName    = name of control to check (including form)



   ' cFieldName = string name of field (for display)



   ' nMinLen    = minimum length of string



   ' nMaxLen    = maximum length of string



   ' nMsg       = flag to show message



   '



   nLen = Len(ctlName.Text)' get length for checking



   If nLen < nMinLen Or nLen > nMaxLen Then



      If nMsg = True Then



         MsgBox cFieldName + " must be between " + Str(nMinLen) + " and " + 



         Str(nMaxLen) + " bytes long.", 0, "Validation Error"



      End If



      ctlName.SetFocus



      CheckSize = False



   Else



      CheckSize = True



   End If



End Function

Listing 6.16 shows the updated cmdOK procedure that shows how CheckSize can be used.

Listing 6.16. The updated cmdOK_Click event that calls the CheckSize function.





Sub cmdOK_Click ()



   Dim nMinLen As Integer



   Dim nMaxLen As Integer



   Dim nOK As Integer



   '



   nMinLen = 3



   nMaxLen = 10



   nOK = True



   '



   ' check highlow field



   nOK = InRange(Me.txtHighLow, "HighLow", 100, 1,True)



   '



   ' check upper field



   nOK = CheckSize(Me.txtUpper, "Upper", 3, 10,True)



   '



   ' check combined field



   If Len(Trim(txtCombined)) = 0 Then



      MsgBox "Combined field is a required field"



      nOK = False



      txtCombined.SetFocus



   End If



   '



   ' check conditional upper/custtype fields



   If Len(Trim(txtUpper)) <> 0 And Len(Trim(cboCustType)) = 0 Then



      MsgBox "If Upper field contains data then the CustType 



      field must contain data"



      nOK = False



      cboCustType.SetFocus



   End If



   '



   ' see if it's all ok



   If nOK = True Then



      Unload Me



   End If



End Sub

The IsValid Library Function

The next function to add to the library is called IsValid. This function can be used to find required fields that have been left blank or are set to null. Listing 6.17 defines the IsValid function. Place this function in LibValid.bas.

Listing 6.17. Code for the IsValid function to check for required fields left blank or set to null.





Function IsValid (ctlName As Control, cFieldName As String,



 nMsg As Integer) As Integer



   If Len(Trim(ctlName.Text)) = 0 Then



      If nMsg = True Then



         MsgBox cFieldName + " is a required field.", 0, "Validation Error"



      End If



      ctlName.SetFocus



      IsValid = False



   Else



      IsValid = True



   End If



End Function

Listing 6.18 shows how to call the IsValid function in a program.

Listing 6.18. The modified cmdOK_Click event to call the IsValid function.





Sub cmdOK_Click ()



   Dim nOK As Integer



   '



   nOK = True



   '



   ' check highlow field



   nOK = InRange(Me.txtHighLow, "HighLow", 100, 1, True)



   '



   ' check upper field



   nOK = CheckSize(Me.txtUpper, "Upper", 3, 10, True)



   '



   ' check combined field



   nOK = IsValid(Me.txtCombined, "Combined", True)



   '



   ' check conditional upper/custtype fields



   If Len(Trim(txtUpper)) <> 0 And Len(Trim(cboCustType)) = 0 Then



      MsgBox "If Upper field contains data then the 



      CustType field must contain data"



      nOK = False



      cboCustType.SetFocus



   End If



   '



   ' see if it's all ok



   If nOK = True Then



      Unload Me



   End If



End Sub

The IsConditional Library Function

The last validation function to add to the library is called IsConditional. This routine has two parameters—both Visual Basic 4 controls. The first is the control field; the second the conditional field. If the control field is not blank, the conditional field must also not be blank. Refer to Listing 6.19 for the IsConditional function.

Listing 6.19. The IsConditional function to check for required entry if a conditional field is completed.





Function IsConditional (ctlMaster As Control, cFldMaster As String,



 ctlChild As Control, cFldChild As String, nMsg As Integer) As Integer



   Dim nMaster As Integer



   Dim nChild As Integer



   '



   ' ctlMaster  = master control



   ' cFldMaster = master field name



   ' ctlChild   = conditional control



   ' cFldChild  = conditional field name



   ' nMsg       = toggle to display message



   '



   nMaster = IsValid(ctlMaster, cFldMaster, False)



   nChild = IsValid(ctlChild, cFldChild, False)



   If nMaster And Not nChild Then



      If nMsg = True Then



         MsgBox "If " + cFldMaster + " is filled in then " + 



         cFldChild + " must be filled in."



         ctlChild.SetFocus



      End If



      IsConditional = False



   Else



      IsConditional = True



   End If



End Function

Notice that the IsConditional function calls the IsValid function with the messages toggled False, and then analyzes and reports the results. Listing 6.20 is the final modification to the cmdOK_Click procedure. This includes all the calls to the LibValid.bas library file.

Listing 6.20. Modified cmdOK_Click event that calls the IsConditional function.





Sub cmdOK_Click ()



   Dim nOK As Integer



   '



   nOK = True



   '



   ' check highlow field



   nOK = InRange(Me.txtHighLow, "HighLow", 100, 1, True)



   '



   ' check upper field



   nOK = CheckSize(Me.txtUpper, "Upper", 3, 10, True)



   '



   ' check combined field



   nOK = IsValid(Me.txtCombined, "Combined", True)



   '



   ' check conditional upper/custtype fields



   nOK = IsConditional(Me.txtUpper, "Upper", Me.cboCustType, "CustType", True)



   '



   ' see if it's all ok



   If nOK = True Then



      Unload Me



   End If



End Sub

The preceding library code samples (Listing 6.10 through Listing 6.20) are meant to show how you can build reuseable input validation routines and employ them in your data entry forms. These routines are by no means an exhaustive list of what you might need or what is possible—it's just a start. But these are useful basic routines that you can insert, modify, and build upon in any Visual Basic 4 project. By creating reuseable routines, you can take advantage of new concepts you learn on each project and incorporate the knowledge into future projects with very little modification.

Now that you have created an input validation library, you are ready to finally create the CompanyMaster data entry screen.

Building the CompanyMaster Input Form

Now that you've learned about data controls and form design in Day 5, "Creating Data Entry Forms with Bound Data Controls" and developed an input validation library in the first part of today's lesson, you are ready to design the first data entry form for the CompanyMaster data table you built on Day 3, "Using the Data Manager."

The following are four basic steps to coding data entry forms in Visual Basic 4:

You can use these steps in coding any forms for Visual Basic 4. You'll follow these steps while you construct the CompanyMaster data entry form.

Definition of the Basic Form

The first step is to set the size of the data entry form, add the input palette and any frames needed. These are the basic components of the form. All other controls will be placed upon the palette within the frames you install in this step.


TIP:

If you put the palette and frames up first, you can place all other controls as so-called children of the palette and frames. This way, when you move the frame, all controls within that frame will also move. The same thing happens when you move the large palette. Creating forms this way makes it easy to make slight adjustments later on.


At this time, you'll also add the data control and the final exit button to the form. Use the information in Table 6.1 and Figure 6.11 as a guide for sizing and placement of the basic form components. Save your form as Mast01.FRM, and save the project as Master.VBP.

Figure 6.11

Basic form components for the CompanyMaster data entry form.

Table 6.1. CompanyMaster Form Components.

Object Property Setting
Form Name
BackColor
Border Style
Caption
Height
Left
Max Button
Top
Width
Save Filename
frmMaster
Light Gray
1—Fixed Single
Company Master
5955
195
False
330
9105
Mast01.FRM
SSPanel Caption
Height
Left
Top
Width
"" (blank)
4815
120
120
8715
Data Control BackColor
Caption
Databasename
FontBold
Height
Left
RecordSource
Top
Width
Light Gray
Company Master
C:\TYSDBVB\CHAP06|MASTER.MDB
False
330
300
CompanyMaster
4500
3615
Command Button Name
Caption
FontBold
Height
Left
Top
Width
cmdExit
E&xit
False
330
7500
5100
1200
SSFrame Caption
FontBold
Height
Left
Top
Width
Company
False
2355
120
180
7155
SSFrame Caption
FontBold
Height
Left
Top
Width
Contact
False
1635
120
2640
3675
SSFrame Caption
FontBold
Height
Left
Top
Width
Other
False
1635
3900
2640
3375

Placement of Input Controls and Prompts

Now you are ready to place the input controls on the form. Each input control has an associated screen prompt. All screen prompts are done using the Visual Basic 4 Label control. You'll use Text box, MaskedEdit, and Check3D controls for input fields. You will also use SSPanel3D controls for the read-only display fields.


NOTE:

Do not double-click controls onto the form. Always single-click the control icon in the Tools Window, and then use the mouse to paint the control within the proper frame control. This will make sure that the controls will be children of the frame control and will move whenever you move the frame. To play it safe, always select the panel by clicking on it prior to selecting a control to place on it.


Because you are using the Win 95 design specifications, you'll need to spend time aligning and sizing controls accordingly. All the information you need to properly size and place the controls is contained in Table 6.2 and Figure 6.12.

Figure 6.12. Adding the input controls and prompts.

Table 6.2. CompanyMaster input controls and prompts.

Object Property Setting
Text Box Name
DataField
DataSource
FontBold
Height
Left
Top
Width
txtCompanyName
CompanyName
Data1
False
330
1380
240
2100
Text Box Name
DataField
DataSource
FontBold
Height
Left
Top
Width
txtAddr1
Addr1
Data1
False
330
1380
660
2100
Text Box Name
DataField
DataSource
FontBold
Height
Left
Top
txtAddr2
Addr2
Data1
False
330
1380
1080
Text Box Name
DataField
DataSource
FontBold
Height
Left
Top
Width
txtCity
City
Data1
False
330
1380
1500
2100
Text Box Name
DataField
DataSource
FontBold
Height
Left
Top
Width
txtCountry
Country
Data1
False
330
1380
1920
2100
SSPanel3D Name
Alignment
BevelOuter
BorderWidth
DataField
DataSource
FontBold
Height
Left
Top
Width
pnlEntryNbr
4—Right Just Middle
1—Inset
1
EntryNbr
Data1
False
330
5160
240
1800
SSPanel3D Name
Alignment
BevelOuter
BorderWidth
DataField
DataSource
FontBold
Height
Left
Top
Width
pnlLastUpdated
4—Right Just Middle
1—Inset
1
LastUpdated
Data1
False
330
5160
660
1800
SSCheck3D Name
Alignment
Caption
DataField
DataSource
FontBold
Height
Left
Top
Width
chkCustFlag
1—Right Justify
Customer Flag:
CustFlag
Data1
False
330
3900
1080
1455
Text Box Name
DataField
DataSource
FontBold
Height
Left
Top
Width
txtStProv
StateProv
Data1
False
330
5160
1500
1800
MaskEdBox Name
DataField
DataSource
FontBold
Height
Left
Mask
PromptInclude
Top
Width
mskPostCode
PostalCode
Data1
False
330
5160
#####-####
False
1920
1800
TextBox Name
DataField
DataSource
FontBold
Height
Left
Top
Width
txtLastName
LastName
Data1
False
330
1380
240
2100
TextBox Name
DataField
DataSource
FontBold
Height
Left
Top
Width
txtFirstName
FirstName
Data1
False
330
1380
660
2100
TextBox Name
DataField
DataSource
FontBold
Height
Left
Top
Width
txtTitle
Title
Data1
False
330
1380
1080
2100
MaskEdBox Name
DataField
DataSource
FontBold
Height
Left
Mask
PromptInclude
Top
Width
mskVoicePhone
VoicePhone
Data1
False
330
1380
(###) ###-####
False
240
1800
MaskEdBox Name
DataField
DataSource
FontBold
Height
Left
Mask
PromptInclude
Top
Width
mskExtension
Extension
Data1
False
330
1380
####
False
660
1800
MaskEdBox Name
DataField
DataSource
FontBold
Height
Left
Mask
PromptInclude
Top
Width
mskFAXPhone
FAXPhone
Data1
False
330
1380
(###) ###-####
False
1080
1800
Label Caption
BackStyle
FontBold
Height
Left
Top
Width
Company Name:
0—Transparent
False
330
120
240
1200
Label Caption
BackStyle
FontBold
Height
Left
Top
Width
Address Line1:
0—Transparent
False
330
120
660
1200
Label Caption
BackStyle
FontBold
Height
Left
Top
Width
Address Line2:
0—Transparent
False
330
120
1080
1200
Label Caption
BackStyle
FontBold
Height
Left
Top
Width
City:
0—Transparent
False
330
120
1500
1200
Label Caption
BackStyle
FontBold
Height
Left
Top
Width
Country:
0—Transparent
False
330
120
1920
1200
Label Caption
BackStyle
FontBold
Height
Left
Top
Width
Entry Number:
0—Transparent
False
330
3900
240
1200
Label Caption
BackStyle
FontBold
Height
Left
Top
Width
Last Updated:
0—Transparent
False
330
3900
660
1200
Label Caption
BackStyle
FontBold
Height
Left
Top
Width
State/Prov:
0—Transparent
False
330
3900
1500
1200
Label Caption
BackStyle
FontBold
Height
Left
Top
Width
Postal Code:
0—Transparent
False
330
3900
1920
1200
Label Caption
BackStyle
FontBold
Height
Left
Top
Width
Last Name:
0—Transparent
False
330
120
240
1200
Label Caption
BackStyle
FontBold
Height
Left
Top
Width
First Name:
0—Transparent
False
330
120
660
1200
Label Caption
BackStyle
FontBold
Height
Left
Top
Width
Title:
0—Transparent
False
330
120
1080
1200
Label Caption
BackStyle
FontBold
Height
Left
Top
Voice Phone:
0—Transparent
False
330
120
240
Label Caption
BackStyle
FontBold
Height
Left
Top
Width
Extension:
0—Transparent
False
330
120
660
1200
Label Caption
BackStyle
FontBold
Height
Left
Top
Width
FAX Phone:
0—Transparent
False
330
120
1080
1200


NOTE:

Please note that we have used the USA nine digit ZIP code in this exercise. You might want to modify this mask if you live in a country that has a different ZIP code format.


You need to add one more set of input controls to the form—The Company Logo controls. Refer to Table 6.3 and Figure 6.13 for sizing and placement of the Image control that holds the picture and the associated label control for the prompt. You will add code behind the image control in the next section.

Figure 6.13

Adding the Company Logo controls.

Table 6.3. CompanyMaster Company Logo controls.

Object Property Setting
Image BorderStyle
DataField
DataSource
Height
Left
Stretch
Top
Width
1—Fixed Single
CompanyLogo
Data1
1200
7380
-1 True
360
1200
Label Caption
BackStyle
FontBold
Height
Left
Top
Width
Company Logo:
0—Transparent
False
330
7380
120
1200

Adding and Coding Command Buttons

Next, add the command buttons. Although you already have the Visual Basic 4 data control on the form, you'll need additional buttons to allow the user to perform adds, deletes, updates, finds, and so on. You'll also add a button to pop up a small form for adding comments to the data record. Refer to Table 6.4 and Figure 6.14 for sizing and placement information.

Figure 6.14

Adding command buttons to Company Master form.

Table 6.4. Company Master Command Buttons.

Object Property Setting
CommandButton Name
Caption
FontBold
Height
Left
Top
Width
cmdAdd
&Add
False
330
7380
1620
1200
CommandButton Name
Caption
FontBold
Height
Left
Top
Width
cmdUpdate
&Update
False
330
7380
2040
1200
CommandButton Name
Caption
FontBold
Height
Left
Top
Width
cmdRestore
&Restore
False
330
7380
2880
1200
CommandButton Name
Caption
FontBold
Height
Left
Top
Width
cmdDelete
&Delete
False
330
7380
3300
1200
CommandButton Name
Caption
FontBold
Height
Left
Top
Width
cmdFind
&Find
False
330
7380
3720
1200
CommandButton Name
Caption
FontBold
Height
Left
Top
Width
cmdNotes
&Notes
False
330
7380
4140
1200

The following code sections should be placed behind each button. You have placed identical code behind other examples earlier this week. Begin with Listing 6.21, which shows the code to enter behind the cmdAdd command button.

Listing 6.21. Adding data records.





Sub cmdAdd_Click ()



   Data1.Recordset.AddNew ' add a new record to table



End Sub

Now add the code in Listing 6.22 to the cmdExit button. This code will unload the form when the exit button is selected.

Listing 6.22. Unloading the CompanyMaster form.





Sub cmdExit_Click ()



  Unload Me ' close myself (better than END)



End Sub

Now enter the code in Listing 6.23 to the cmdFind_Click event. When executed, this code will query the user to enter an appropriate search string.

Listing 6.23. Finding data records.





Sub cmdFind_Click ()



   Dim nResult As Integer



   Dim cFind As String



   Dim cBookmark As String



   '



   cFind = InputBox("Enter Search String:", "CompanyMaster FIND")



   If Len(cFind) > 0 Then



      cBookmark = Data1.Recordset.Bookmark



      Data1.Recordset.FindFirst cFind



      If Data1.Recordset.NoMatch Then



         MsgBox "Can't Find [" + cFind + "]", 0, "Find Error"



         Data1.Recordset.Bookmark = cBookmark



      End If



   End If



End Sub

The code in listing 6.24 should be entered into the cmdRestore_Click event to restore the controls to their original value when the cmdRestore command button is selected.

Listing 6.24. Restoring the data controls.





Sub cmdRestore_Click ()



   Data1.UpdateControls ' restore controls from table



End Sub

Now enter code to save the data. Use Listing 6.25 as a guide and enter this code into the cmdUpdate_Click event.

Listing 6.25. Writing a record.





Sub cmdUpdate_Click ()



   Data1.RecordSet.Update ' write reocord to table



End Sub

Listing 6.26 contains the code that should now be entered into the cmdDelete_Click event. This code will delete the displayed record after the user confirms the deletion.

Listing 6.26. Deleting a record.





Sub cmdDelete_Click ()



   Dim nResult As Integer



   '



   ' give user chance to reconsider



   nResult = MsgBox("Are you sure?", 1, "Delete Record")



   If nResult = 1 Then



      Data1.Recordset.Delete



   End If



End Sub

You need to add code behind the Image control to allow users to update the CompanyLogo field. Users should be able to locate a file on the disk, then save it to the field. The form will then display the saved image. You can give users access to loading files by adding the Visual Basic 4 CommonDialog control to the form. Select the CommonDialog control from the Tools Window and place it at the bottom of the form. It does not really mater where it is placed—the command dialog control is invisible at runtime. Once the control is on the form, add the code in Listing 6.27 to the Image1_DblClick event.

Listing 6.27. Updating the company logo.





Sub Image1_DblClick ()



   '



   ' set dialog properties



   CMDialog1.Filter = "Bitmap (*.bmp)|*.bmp|Icon (*.ico)|*.



ico|Metafiles (*.wmf)|*.wmf|"



   CMDialog1.DialogTitle = "Load Company Logo"



   '



   ' run dialog box



   CMDialog1.Action = 1



   '



   ' if they picked a file, load it up



   On Error GoTo PicErr ' in case user picks a bad file



   If Len(CMDialog1.Filename) <> 0 Then



      Image1.Picture = LoadPicture(CMDialog1.Filename)



   End If



   On Error GoTo 0 ' trun off error trapping



   '



   ' all done, go to exit



   GoTo PicExit



   '



   ' handle bad picture error



PicErr:



   MsgBox "Unable to load selected file.", 0, "Picture Error"



Resume Next



   '



   ' final exit of procedure



PicExit:



End Sub

The code in Listing 6.27 sets file type and caption properties of the common dialog box, runs the dialog and then, if a file has been selected, attempts to save it to the image control. You add a little error trapping here in case the user selects an invalid file type.

Adding Input Validation

The last step in creating Visual Basic 4 data entry forms is adding the input validation routines. The following is a list of the input rules you should use when coding the validation routines:

You can perform all these validation checks in a single procedure—the ValidateForm() function. Listing 6.28 shows the code that fulfills the validation rules. Create a new function called ValidateForm and insert this code.

This code calls routines from the IsValid module created earlier today. Add this module to the project by right-clicking on the Project window and selecting the Add menu item.

Listing 6.28. Performing validation checks.





Function ValidateForm () As Integer



   Dim nOK As Integer



   Dim nValidErr As Integer



   '



   ' perform input validations



   nOK = IsValid(Me.txtCompanyName, "Company Name", True)



   If nOK = False Then



      nValidErr = True



   End If



   '



   nOK = IsValid(Me.txtAddr1, "Address Line1", True)



   If nOK = False Then



      nValidErr = True



   End If



   '



   nOK = IsValid(Me.txtCity, "City", True)



   If nOK = False Then



      nValidErr = True



   End If



   '



   nOK = IsValid(Me.txtStProv, "State/Prov", True)



   If nOK = False Then



      nValidErr = True



   End If



   '



   nOK = IsValid(Me.txtCountry, "Country", True)



   If nOK = False Then



      nValidErr = True



   End If



   '



   nOK = IsValid(Me.mskPostCode, "Postal Code", True)



   If nOK = False Then



      nValidErr = True



   End If



   '



   nOK = IsConditional(Me.txtAddr2, "Address Line2", Me.txtAddr1, 



   "Address Line1", True)



   If nOK = False Then



      nValidErr = True



   End If



   '



   nOK = IsConditional(Me.txtFirstName, "First Name", 



   Me.txtLastName, "Last Name", True)



   If nOK = False Then



      nValidErr = True



   End If



   '



   nOK = IsConditional(Me.mskExtension, "Extension", 



   Me.mskVoicePhone, "Voice Phone", True)



   If nOK = False Then



      nValidErr = True



   End If



   '



   ' set return value



   If nValidErr = True Then



      ValidateForm = False



   Else



      ValidateForm = True



   End If



End Function

After you enter this code, you need to add a few lines to the Data1_Validate event. The code in Listing 6.29 calls the validation routine each time the Update button is clicked or the arrow keys are pressed on the data control.

Listing 6.29. Calling validation routines when the Update button is pressed.





Sub Data1_Validate (Action As Integer, Save As Integer)



   Dim nResult As Integer



   '



   nResult = ValidateForm()



   If nResult = False Then



      Save = 0' cancel update



      MsgBox "Update Cancelled", 0, "Update Error"



   End If



End Sub

In Listing 6.29, if an error is returned by the ValidateForm() function, the save action is canceled and a warning message is displayed.

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:

You also learned that you should ask yourself a few basic questions when you are developing validation rules for your form.

You learned how to write keyboard filter validation functions using the Visual Basic 4 KeyPress event. You learned how to write field level validation functions that check for valid input ranges, input that is part of a list of valid data, and input that is within minimum and maximum length requirements. You also learned how to write validation functions that make sure dependent fields have been filled out properly. Finally, you learned how to create a Visual Basic 4 library module containing validation functions that can be used in any Visual Basic 4 program.

You also applied your knowledge of bound data controls, Visual Basic 4 data entry form design, and validation processing to create the data entry form for the CompanyMaster data table.

Quiz

  1. What is the difference between input validation and error trapping?
  2. What value must you subtract from a lower case character to get its upper case ASCII value?
  3. What Visual Basic 4 event occurs every time a key is pressed on your keyboard?
  4. Do characters in a validation list need to be entered in any particular order?
  5. What does the following code mean?

    
    
    
    
    If Len(Trim(txtUpper)) <> 0 then
  6. Should conditional field validation be performed at the field level or the form level?
  7. When should you load validation lists?
  8. What do the three sections of the format property of the MaskedEdit control represent? What character separates these sections?

Exercises

  1. Write code to allow entry of only capital letters in a field. The user should be able to enter control codes, but not numbers or symbols.
  2. Write the format property for a MaskedEdit control that rounds the entered number to the nearest hundredth, includes commas in all numbers, and places an en dash (-) in front of negative numbers.
  3. Write a form level validation routine that requires that entry be made into a field named txtDate before a record can be saved by pressing a button named cmdOK.

    Write the code to fill a combo box named cboEmployees with your employee's last names of Smith, Andersen, Jones, and Jackson. What property do you set in the combo box control to sort these names alphabetically?