Implementing unbound columns using Recordset clones

The simplest way to gather the data from other columns is to clone the Recordset, move to the specified bookmark, and get the data from the clone, as in the following example:

Example Title	Copy Code
Private Sub TDBList1_UnboundColumnFetch(Bookmark As Variant, _ ByVal Col As Integer, Value As Variant) Dim myclone As Recordset Set myclone = Data1.Recordset.Clone() myclone.Bookmark = Bookmark Value = myclone.Fields(Col) End Sub

Example Title

Copy Code

Private Sub TDBList1_UnboundColumnFetch(Bookmark As Variant, _

        ByVal Col As Integer, Value As Variant)

 

    Dim myclone As Recordset

    Set myclone = Data1.Recordset.Clone()

 

    myclone.Bookmark = Bookmark

    Value = myclone.Fields(Col)

End Sub

Although this example is functional, it would be more efficient to make the clone a global object, as it would no longer be necessary for Visual Basic to create it with each call to the event. A good place to do this is in the Form_Load event, which fires before the list is displayed:

Example Title	Copy Code
Dim ucfClone As Recordset ' Global UnboundColumnFetch clone Private Sub Form_Load() Data1.Refresh ' Make sure the recordset is created first Set ucfClone = Data1.Recordset.Clone() End Sub Private Sub TDBList1_UnboundColumnFetch(Bookmark As Variant, _ ByVal Col As Integer, Value As Variant) ucfClone.Bookmark = Bookmark Value = ucfClone.Fields(Col) End Sub

Example Title

Copy Code

Dim ucfClone As Recordset ' Global UnboundColumnFetch clone

 

Private Sub Form_Load()

    Data1.Refresh ' Make sure the recordset is created first

    Set ucfClone = Data1.Recordset.Clone()

End Sub

 

Private Sub TDBList1_UnboundColumnFetch(Bookmark As Variant, _

        ByVal Col As Integer, Value As Variant)

 

    ucfClone.Bookmark = Bookmark

    Value = ucfClone.Fields(Col)

End Sub

You can speed things up even more by using a Field object, creating it from the clone's Fields collection. This is faster because Visual Basic does not need to locate the correct field each time the event is called:

Example Title	Copy Code
Dim ucfClone As Recordset ' Global UnboundColumnFetch clone Dim ucfField As Field ' Global UnboundColumnFetch field Private Sub Form_Load() Data1.Refresh Set ucfClone = Data1.Recordset.Clone() Set ucfField = ucfClone.Fields(1) End Sub Private Sub TDBList1_UnboundColumnFetch(Bookmark As Variant, _ ByVal Col As Integer, Value As Variant) ucfClone.Bookmark = Bookmark Value = ucfField End Sub

Example Title

Copy Code

Dim ucfClone As Recordset ' Global UnboundColumnFetch clone

Dim ucfField As Field     ' Global UnboundColumnFetch field

 

Private Sub Form_Load()

    Data1.Refresh

    Set ucfClone = Data1.Recordset.Clone()

    Set ucfField = ucfClone.Fields(1)

End Sub

 

Private Sub TDBList1_UnboundColumnFetch(Bookmark As Variant, _

        ByVal Col As Integer, Value As Variant)

 

    ucfClone.Bookmark = Bookmark

    Value = ucfField

End Sub

After the ucfField object is initialized in Form_Load, it will always contain the data in Field 1 of the current row of the clone. If the underlying database field allows null values, you should test the Field object first before assigning its data to the Value argument:

Example Title	Copy Code
Private Sub TDBList1_UnboundColumnFetch(Bookmark As Variant, _ ByVal Col As Integer, Value As Variant) ucfClone.Bookmark = Bookmark If Not IsNull(ucfField) Then Value = ucfField End Sub

Using Field objects is an elegant approach. Not only is it more efficient, but it frees you from keeping track of collection indexes throughout your code. For example, given a Rectangle table containing Length and Width fields, the following code implements an unbound column that uses Field objects to calculate the area:

Example Title	Copy Code
Dim ucfClone As Recordset ' Global UnboundColumnFetch clone Dim ucfLength As Field Dim ucfWidth As Field Private Sub Form_Load() Data1.Refresh Set ucfClone = Data1.Recordset.Clone() Set ucfLength = ucfClone.Fields("Length") Set ucfWidth = ucfClone.Fields("Width") End Sub Private Sub TDBList1_UnboundColumnFetch(Bookmark As Variant, _ ByVal Col As Integer, Value As Variant) ucfClone.Bookmark = Bookmark Value = ucfLength * ucfWidth ' Calculate "Area" column End Sub

Example Title

Copy Code

Dim ucfClone As Recordset ' Global UnboundColumnFetch clone

Dim ucfLength As Field

Dim ucfWidth As Field

 

Private Sub Form_Load()

    Data1.Refresh

    Set ucfClone = Data1.Recordset.Clone()

    Set ucfLength = ucfClone.Fields("Length")

    Set ucfWidth = ucfClone.Fields("Width")

End Sub

 

Private Sub TDBList1_UnboundColumnFetch(Bookmark As Variant, _

        ByVal Col As Integer, Value As Variant)

 

    ucfClone.Bookmark = Bookmark

    Value = ucfLength * ucfWidth ' Calculate "Area" column

End Sub