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

PeopleWatcher uses two combo box controls. To ensure that the user selects only valid choices from the lists rather than typing in an invalid value, the Style property for both of these combo boxes is set to 2—Dropdown List. When the data control moves to a new record, Visual Basic uses data from the record to set the values of the combo boxes. If the value it obtains from the database is not a valid choice for a combo box, the system generates the message, “‘Text’ property is read only.” This is its cryptic way of saying the text value it got from the database is not valid in the combo box.

To prevent this and similar problems, the values in the database must always fit into the controls designed to hold them. To prevent this specific problem in PeopleWatcher, the fields corresponding to the combo boxes have default values in the Employees table. This gives them legal values if values are not specified when a record is created.

For instance, the HomeState field contains the abbreviation for the state in which the employee lives. Using the design capabilities of the Data Manager, this field was given the default value AK. If the user, the program, or even some other program creates a new employee record and does not specify a value for HomeState, the database automatically gives the field the value AK. When the record is displayed by PeopleWatcher, HomeState contains a valid value so there is no error message. PeopleWatcher will not allow the user to select an invalid value for this field, so the value should always remain valid.

Of course, someone could change the value of the field using Data Manager or some other program. In that case, the user can enter a value that will be rejected by the combo box, so a user may later see the error message.

Values Not Allowed by Validation When a user makes changes to an employee record and tries to accept the changes, the program’s validation routines verify that the data entered is reasonable. If the program also attempts to verify data to which the user does not have access, the validation routines can cause trouble.

For example, suppose the user is a member of the Secretary group. This user has read access to Basic, Home, and HR data and update access to Basic data. The user updates an employee’s Basic data and selects the Accept Changes command from the Edit menu. Now suppose the Home data’s Zip Code field in the database was somehow given the value 803X1, which is not a valid Zip Code value. If the form validation routines detect this problem, they will present an error message asking the user to fix the Zip Code. Because this user does not have update access to the Zip Code field, the necessary correction is impossible. The only thing the user can do is cancel the changes and report the problem to someone else who does have update permission for the Zip Code field.

To prevent this kind of problem, form validation routines should not attempt to validate fields that the user cannot update. The validation routines used by PeopleWatcher check each control’s Locked property before validating its data. If Locked is true, the user cannot edit the field so the routine skips the validation.

The following code shows the ValidateDigits function. After checking that the field is editable, the function calls ValidDigits to verify that the user has entered only digits in the field. The other data validation routines are similar.

Public Function ValidateDigits(ctl As TextBox, msg As String) As Boolean
Dim txt As String
    ‘ Skip noneditable fields.
    If ctl.Locked Then
        ValidateDigits = False
        Exit Function
    End If
  
    txt = ctl.Text
    If ValidDigits(txt) Then
        ValidateDigits = False
    Else
        PresentMessage ctl, msg, vbOKOnly, _
            “Invalid Digit Format”
        ValidateDigits = True
    End If
End Function

Public Function ValidDigits(txt As String) As Boolean
Dim i As Integer
Dim ch As String

    For i = 1 To Len(txt)
        ch = Mid$(txt, i, 1)
        If (ch < “0” Or ch > “9”) Then
            ValidDigits = False
            Exit Function
        End If
    Next i
    ValidDigits = True
End Function

Testing Database Applications

Data controls are quite complicated. Whenever an application uses a data control, all of the possible actions the data control might take should be thoroughly tested. Particular attention should be given to unusual conditions such as these:

•  Moving past the last record

•  Moving before the first record

•  Executing a query that returns no rows

•  Working with a completely empty database

PeopleWatcher does not correctly handle a completely empty database. If a user deletes all of the records, the program will generate an error message when it tries to build an empty employee list. If the user then types in one of the text fields, the program generates an untrapped error and crashes when it tries to edit the nonexistent current record.

Testing for these odd cases was left out of PeopleWatcher to make it a little less complicated. Because this application is a corporate personnel file, the database should never be empty in any case. A true production application should handle all of these special cases, even if they should never occur in actual use.

Summary

PeopleWatcher demonstrates several important database programming concepts. It uses a data control and bound controls to display data and to allow the user to edit the data. It uses a Recordset object to manipulate the data behind the scenes. For example, it uses the Recordset object to build a list of employee names in an outline control without displaying all of the data in the bound controls.

PeopleWatcher also shows how an application can configure itself to meet the requirements of different groups of users. Using a relatively simple password and privilege system, PeopleWatcher customizes itself to meet the widely varying needs of the users. This not only makes the application appear sensitive to each user’s demands, but it also makes the program more widely usable. Without this sort of customization, an application such as PeopleWatcher that deals with sensitive information could be used by only a few privileged people.