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

The event handler can also validate the data entered by the user and decide whether to allow the operation that caused the event. For example, suppose the Action parameter initially has the value vbDataActionUpdate, indicating that changes to the current record are about to be saved. The program could examine the data to make sure the data values make sense. It could check that data values entered in date fields have valid date formats, that numeric values lie within certain ranges, and so forth. If the values do not pass validation, the subroutine can present an error message asking the user to fix the problems. It can then set the Action parameter to vbDataActionCancel to indicate that the update should not take place. The user can click the program’s Accept button again after fixing the problems in the data.

For another example, suppose the user begins editing a record and then invokes the Close command in the application’s control box. The program needs to know whether to save the changes to the data before it exits. Before the data control’s form unloads, Visual Basic invokes its Validate event handler. This routine can ask the user if the pending changes should be saved before quitting. If the user wants to save the changes, the program should set the Save parameter to true. If the user wants to discard the changes, the program should set Save to false. If the user decides to cancel the operation and not unload the form, the program should set Action to vbDataActionCancel. In that case, the form’s unload operation will not occur, and the user can resume modifying the record.

Bookmarks Each record in a recordset has an associated bookmark. A bookmark is a set of bytes that uniquely identifies that record in that particular recordset. If a program saves a record’s bookmark in a string, it can later quickly return to that record by setting the recordset’s Bookmark property to the string’s value. The following code fragment shows how a program could use a bookmark to return to a record quickly after performing other operations on a recordset.

Dim rs As Recordset
Dim bm As String        ‘ The bookmark.

    ‘ Initialize the recordset, etc.
    :
    ‘ Save the bookmark.
    bm = rs.Bookmark

    ‘ Perform other operations with the recordset.
    :
    ‘ Return to the original record.
    rs.Bookmark = bm

Bookmarks are not shared among recordsets. Even though two recordsets may contain the exact same records, bookmarks from one are not usable in the other.

Understanding PeopleWatcher

As has already been mentioned, PeopleWatcher is a personnel file application. Using a data control and bound text boxes, one could build a simple version of this program, but it would have several problems. The program would use a data control that selected data from an Employee table containing fields for the employees’ last names, first names, Social Security numbers, salaries, and so forth. Data bound controls would display the record information. Without including a line of Visual Basic source code, this program could display and update personnel records. Unfortunately, this simplified personnel system has several problems.

First, locating records would be difficult. The user would need to start at the beginning of the data control’s recordset and step through the records one at a time until reaching the correct record. If the database contains only a dozen or so employee records, this is not a big problem. If the personnel file contains records for hundreds or thousands of employees, however, this would be impractical. The program needs a search facility or some other mechanism to make locating specific records easier.

This program would also be usable by only a few people in the company. Because the Employee table contains sensitive information, such as employee Social Security numbers and salaries, only a few users would be able to use the system without violating employee privacy.

The following sections explain how the PeopleWatcher program solves these problems. They also describe in detail how PeopleWatcher implements standard database operations such as creating, modifying, and deleting records.

Managing the Outline Control

PeopleWatcher uses an outline control to provide a list of employee names grouped by their last initials. To locate the record for Michelle Stephens, the user scrolls through the list to find the entry for the initial S. Clicking on the plus sign next to the letter S then opens the entry to display a list of names with that initial. Unless the database is extremely large, finding a particular entry in the list is easy.

Figure 8.6 shows PeopleWatcher displaying the record for employee Michelle Stephens. The outline control is on the left of the screen with the entry for this record highlighted.

The LoadNameOutline subroutine that follows uses Recordset methods to fill the NameOutline control with a list of employee names grouped by last initial. Initially the Recordset variable TheRS is attached to the data control EmployeeData. To prevent the data control from updating bound text boxes as the routine examines each record in the Recordset, LoadNameOutline begins by setting EmployeeData.Recordset to Nothing.

After it has finished building the list of employee names, the subroutine moves the Recordset object back to its first record and sets the EmployeeData.Recordset property back to TheRS. This restores the link between the Recordset and the data control. It also makes the data control immediately update the text boxes and other bound controls to display the data for the Recordset’s first record.

Sub LoadNameOutline()
Dim query As String
Dim i As Integer
Dim j As Integer
Dim last_name As String
Dim first_name As String
Dim letter As Integer
Dim new_letter As Integer

    ‘ Start with an empty list.

    NameOutline.Clear
  
    ‘ Disconnnect the Data control.
    Set EmployeeData.Recordset = Nothing
  
    ‘ Get the employee names.
    On Error GoTo LoadNameError
    query = “SELECT * FROM Employees ” & _
        “ORDER BY LastName, FirstName”
    Set TheRS = TheDB.OpenRecordset(query, dbOpenDynaset)
  
    ‘ Load the names.
    letter = Asc(“A”) - 1
    i = 0
    Do Until TheRS.EOF
        last_name = TheRS!LastName
        first_name = TheRS!FirstName
        new_letter = Asc(UCase$(Left$(last_name, 1)))
        If new_letter <> letter Then
            ‘ Add letters.
            For j = letter + 1 To new_letter
                NameOutline.AddItem Chr$(j)
                NameOutline.Indent(i) = 1
                i = i + 1
            Next j
            letter = new_letter
        End If
      
        ‘ Add the name.
        NameOutline.AddItem last_name & “, ” & first_name
        NameOutline.Indent(i) = 2
        Bookmarks.Add CStr(TheRS.Bookmark)
        NameOutline.ItemData(i) = Bookmarks.Count
        i = i + 1
      
        TheRS.MoveNext
    Loop
  
    ‘ Add remaining letters.
    For j = letter + 1 To Asc(“Z”)
        NameOutline.AddItem Chr$(j)
        NameOutline.Indent(i) = 1
        i = i + 1
    Next j
  
    ‘ Reconnnect the Data control. This generates
    ‘ a Reposition event.
    TheRS.MoveFirst
    Set EmployeeData.Recordset = TheRS
  
    Exit Sub

LoadNameError:
    Beep
    MsgBox “Error” & str$(Err.Number) & _
        “ reading employee names.” & vbCrLf & _
        vbCrLf & Err.Description, _
        vbOKOnly + vbInformation, _
        “Error Reading Names”
    Exit Sub
End Sub

FIGURE 8.6  PeopleWatcher displaying the record for Michelle Stephens.