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

The value dbOpenDynamic creates a Recordset similar to a dynaset. Any changes made to the data by other users will be reflected in the dynamic Recordset.

The value dbOpenSnapshot makes the OpenRecordset method create a snapshot-type Recordset. Snapshots contain a copy of the data as it existed when the snapshot was created. If another database user adds, deletes, or modifies a record, the snapshot will not know about the change unless it is recreated. Snapshots can select records using an SQL statement much as a dynaset can.

The value dbOpenForwardOnly creates a Recordset similar to a snapshot, but the program can only move forward through the records, thereby providing faster performance than a snapshot.

Table Recordsets are generally the fastest. Dynasets are somewhat slower but give much more flexibility. Snapshots provide a balance between the two. They can select records using SQL statements, but they do not allow records to be updated. Because they are generally faster than dynasets, however, they are a better choice if the program will not need to update the records selected.

Manipulating Data with Recordsets After an application creates a Recordset, the Recordset is considered to point at a “current” record. Recordset methods allow a program to manipulate the current record. For example, the Recordset’s Delete method deletes the current record from the database.

Visual Basic code can use an exclamation mark (!) to access the value of a particular field within a Recordset’s current record. For example, rs!LastName refers to the value of the LastName field in the rs Recordset’s current record.

A program can also retrieve a field’s value from a Recordset’s Fields collection by specifying the field’s name as the index for the collection. Using this method, the LastName field in the rs Recordset’s current record is rs.Fields(“LastName”). This method is particularly useful for programs that do not know which fields will be accessed until run time.

Other Recordset methods allow a program to make other records become the current record. The MoveNext and MovePrevious methods move the current record to the next or previous record, respectively. The MoveFirst and MoveLast methods move the current record to the first or last record in the Recordset.

A Recordset’s BOF (beginning of file) property is true if the Recordset is positioned before the first record in the recordset. Similarly, the EOF (end of file) property is true if the Recordset is positioned after the last record. The following code uses the MoveNext method and the EOF property to build a text string listing all of the names in the Employees table:

Dim db As Database
Dim rs As Recordset
Dim query As String
Dim txt As String

    ‘ Open the database.
    Set db = DBEngine.Workspaces(0).OpenDatabase(“Employees.MDB”)

    ‘ Create the Recordset.
    query = “SELECT LastName, FirstName FROM Employees ” & _
        “ORDER BY LastName, FirstName”
    Set rs = db.OpenRecordset(query, dbOpenSnapshot)
  
    ‘ Build the list of names.
    txt = “”
    Do Until TheRS.EOF
        txt = txt & rs!LastName & “, ” & rs!FirstName & vbCrLf
        TheRS.MoveNext
    Loop

Checking the BOF and EOF properties is important whenever a program manipulates data using a Recordset. Many of the Recordset methods fail if there is no current record. For example, if EOF is true (the program is beyond the last record) and the program executes the MoveNext command, Visual Basic generates run time error 3021: No current record. The program will also receive this error if it invokes the Delete method when there is no current record.

Creating Records A program can create a new record by invoking a Recordset object’s AddNew method. This method creates a memory buffer to contain the new record. If the Recordset is associated with a data control, any fields bound to the control are cleared so the user can enter data for the new record.

The program can provide Accept and Cancel buttons to allow the user to accept the data and create a new record or to cancel the operation and not create a new record. If the user clicks the Accept button, the program should invoke the Recordset’s Update method to create the new record. If the user clicks the Cancel button, it should invoke the CancelUpdate method.

Editing Records If the user begins typing in a bound control, the attached data control automatically begins editing its current data record. An application can initiate editing programmatically by calling the Recordset object’s Edit method.

As is the case when creating a new record, the program should allow the user to accept or cancel changes to the data. If the user clicks the Accept button, the program should invoke the Recordset’s Update method to update the database. If the user clicks the Cancel button, it should invoke the CancelUpdate method to leave the database unchanged.

Copying Records The Recordset object does not directly provide a method for copying records. To give users this capability, a program should first save the data values for the record to be copied into variables. It could save the values in a collection. Next it should use the Recordset’s AddNew method to allocate memory for a new record. It should then copy the saved values into the new record’s fields. The easiest way to do that is to copy the saved values into bound controls. The program can then allow the user to edit the copied data before accepting or canceling the changes.

Deleting Records To delete a record, a program can use the Recordset’s Delete method. The Delete method immediately removes the Recordset’s current record permanently from the database.

When creating a new record or editing an existing one, the program can allow the user to accept or cancel the operation. Similarly, it should give the user a chance to cancel a delete operation. Because the Delete method takes effect immediately and is irreversible, the program should first ask the user to confirm that it should delete the record. It can do this using a message box, as shown in the following code:

Private Sub mnuEditDelete_Click()
    If MsgBox(“Are you sure you want to delete this record?”, _
        vbYesNo, “Delete Record?”) = vbYes _
    Then
        ‘ Delete the record.
        rs.Delete
    End If
End Sub

Validation A data control receives a Validate event just before a new record becomes the current record. It also receives the Validate event before the Update, Delete, Close, or Unload operations. In all of these cases, changes to the data may be pending. The program’s Validate event handler can take actions to verify the correctness of the data and either save or discard the changes.

The event handler’s Action and Save parameters can help the program determine what sort of event is in progress. The Action parameter indicates the action that is about to occur—update, delete, and so forth. The event handler can set Action to the value vbDataActionCancel to cancel the action.

The Save parameter initially indicates whether bound data has changed. The program can set Save to false if it wants to continue the action but not save the changes.