Using a Record Set

The Fields of a Recordset

Any of the techniques we have used above, whether using the OpenRecordset() method of an object, a SQL statement or the name of a table passed to the Open() method of a Recordset object, is used to select the records. The records are primarily identified by the columns that organize them., To recognize the columns of a record set, each Recordset object of the libraries we have so far is equipped with a property named Fields, which is a collection of the columns of the record set.

To visit a Fields collection, you can use a For Each...In...Next conditional statement.

Each column of the Fields collection is an object of type Field, which is the common name of a column in database systems. In our introduction to columns in Lesson 9, we saw that a name was its most fundamental characteristic. To identify the name of a column, the Field object is equipped with a property named Name. Here is an example of accessing it:

Private Sub cmdVideoData_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Open "Videos", _
                   Application.CodeProject.Connection, _
                   adOpenStatic, adLockOptimistic, adCmdTable
    
    For Each fldEach In rstVideos.Fields
        MsgBox fldEach.Name
    Next
    
    rstVideos.Close
    Set rstVideos = Nothing
End Sub

When this code executes, it will display the name of each column in a message box, one at a time.

 

The Value of a Record

In Lesson 12, we saw that a record was created by entering a value under a column in a table or in a Windows control of a form. To identify the value held by a column, the Field object is equipped with a property named Value. Here is an example of using it:

Private Sub cmdVideoAnalyze_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Source = "SELECT Title, Director, CopyrightYear, Rating FROM Videos"
    rstVideos.ActiveConnection = Application.CodeProject.Connection
    rstVideos.CursorType = adOpenStatic
    rstVideos.LockType = adLockOptimistic
    rstVideos.Open
    
    For Each fldEach In rstVideos.Fields
        MsgBox fldEach.Value
    Next
    
    rstVideos.Close
    Set rstVideos = Nothing
End Sub

When this code executes, it visits each column, retrieves its value, and displays it in a message box.

 

Record Navigation in a Record Set

 

Introduction

Navigating through a record set consists of visiting its records. This is similar to using the navigation buttons at the bottom of a table, a query, a form or a report. We saw how to do this manually. We also saw how to do this programmatically using the DoCmd object. The Recordset object of the ADO library supports record navigation through various methods.

 

Moving Among Records

Whenever performing an operation on a record, you should know your position in the set. Whenever in doubt, you can reset your position by moving to the first record. To support this, the Recordset object is equipped with a method named MoveFirst. This method takes no argument. Here is an example of calling it:

Private Sub cmdVideoAnalyze_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Source = "SELECT Title, Director, CopyrightYear, Rating FROM Videos"
    rstVideos.ActiveConnection = Application.CodeProject.Connection
    rstVideos.CursorType = adOpenStatic
    rstVideos.LockType = adLockOptimistic
    rstVideos.Open
    
    rstVideos.MoveFirst
    
    For Each fldEach In rstVideos.Fields
        MsgBox fldEach.Value
    Next
    
    rstVideos.Close
    Set rstVideos = Nothing
End Sub

Besides the first record, another extreme position you can move to is the last record. To do this, you can call the MoveLast() method of the Recordset object.

To move from one record to the next, you can call the MoveNext() method of the Recordset object. Here is an example of calling it:

Private Sub cmdVideoAnalyze_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Source = "SELECT Title, Director, CopyrightYear, Rating FROM Videos"
    rstVideos.ActiveConnection = Application.CodeProject.Connection
    rstVideos.CursorType = adOpenStatic
    rstVideos.LockType = adLockOptimistic
    rstVideos.Open
    
    rstVideos.MoveFirst
    rstVideos.MoveNext
    For Each fldEach In rstVideos.Fields
        MsgBox fldEach.Value
    Next
    
    rstVideos.Close
    Set rstVideos = Nothing
End Sub

When this code executes, the record position is first moved to the first. Then it immediately moves to the second record. Then it visits each column, retrieves its value corresponding to the second record and displays it in a message box.

To move to the previous record in the set, call the MovePrevious() method of the Recordset object.

Some, if not most operations require that you remain within the range of values of the record set. If you move below the first record record, you (actually the user) may receive an error. In the same way, if you move beyond the last record, you would receive an error. To assist you with checking whether you are in the first record, the Recordset object provides the BOF() method. This method returns a Boolean value as follows:

• If it returns TRUE, then you are currently positioned before the first record
• If it returns FALSE, then you are at or above the first record

On the other hand, if you want to check whether you are at the highest position of the records, you can call the EOF() method of the Recordset object. It also returns a Boolean value as follows:

• If it returns TRUE, then you are currently positioned after the last record
• If it returns FALSE, then you are at or below the last record

Practical Learning: Navigating Among Records