Opening the Database and Instancing the Recordset

To open the database and instance the Recordset object behind one unbound form, the following design is recommended:

Use module-level variables of type Recordset and Database.

The load event of the form should call one or more functions of type boolean that return true when the .MDB file (the database) is opened sucessfully and when a record set is ready. When these conditions are met, a procedure should be called to display the data. The form on page 17-2 shows only one record at a time. This design avoids the complex issues associated with showing several rows of editable data.

The form should have navigation buttons.

The form should have "find," "new record," "delete," and "close" buttons.

There should be module-level variables of type Boolean that track when data is being changed or added.

Displaying Data on the Form

Page 17-7 shows the procedure used to display data one record at a time. Note that database records can contain Null values, but VB TextBox controls cannot handle Nulls. The way to avoid this problem is to concatenate a zero-length string to the value of Recordset.Fields(<index>). Since the value Empty is equivalent to a zero-length string we have the form

With rsDAO
    If .RecordCount Then
        txtControl1.Text = .Fields("Field1") & Empty
        txtControl2.Text = .Fields("Field2") & Empty
        txtControl3.Text = .Fields("Field3") & Empty
    End If
End With

Or, using the style in the book, we have the form

With rsDAO
    If .RecordCount >0 Then
        txtControl1.Text = !Field1 & Empty
        txtControl2.Text = !Field2 & Empty
        txtControl3.Text = !Field3 & Empty
    End If
End With

which does not reflect the fact that database fields are part of a collection. To quickly verifiy that this concatenation actually works, type the following in the Immediate (Debug) Window:

?Null & Empty = Empty

This should return True.