Home

Using a Record Set

 

The Fields of a Recordset

 

The Fields Collection

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.

A Field of the Fields

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.

 

Don't Move Beyond the Extremes

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 LearningPractical Learning: Navigating Among Records

  1. Open the ROSH database
  2. Open the Students form that you were working on in Lesson 15 and switch to Design View
  3. Heighten the Form Footer section to create some space below the existing controls.
    You will design it as follows:
     
  4. On the Toolbox, click the Combo Box and click to the left under the SortBy combo box. If the Combo Box Wizard starts, click Cancel
  5. Set the caption of the accompanying label to Show Records Where the:
  6. Change the following properties for the combo box:
    Name: cboColumnNames2
    Row Source Type: Value List
  7. Make sure the Control Wizard button of the Toolbox is down. On the Toolbox, click the Combo Box and click on the right side of the previously added combo box in the Form footer section
  8. In the first page of the Combo Box Wizard, click the second radio button (I Will Type In The Values That I Want) and click Next
  9. Click under Col1 and type =
  10. Press the down arrow key and type <>
  11. Press the down arrow key and type <
  12. Press the down arrow key and type <=
  13. Press the down arrow key and type >
  14. Press the down arrow key and type >=
     
  15. Click Next
  16. Accept the first radio button and click Next
  17. Click Finish
  18. In the Properties window, change the name of the new combo box to cboOperators
  19. Change the Column Widths and the List Width values to 0.35
  20. Click the Default Value field and type "="
  21. Delete the accompanying label of the new combo box
  22. On the Toolbox, click Combo Box and click on the right side of the newly added combo box. If/When the Combo Box Wizard starts, click Cancel
  23. In the Properties window, change the name of the new combo box to cboValues
  24. Set its Row Source Type to Value List and delete its accompanying label
  25. On the Toolbox, click Command Button and click on the right side of the previously added combo box
  26. Change its properties as follows:
    Name: cmdSubmitFilter
    Caption: Submit Filter

     
  27. On the Toolbox, click Text Box and click just on top of the previously added combo box
  28. Delete its accompanying label and change its properties as follows:
    Name: txtRegularString
    Visible: False
     
  29. Save the form
  30. On the form, click the lower left combo box
  31. In the Events tab of the Properties window, double-click After Update and click its ellipsis button
  32. Implement it as follows:
     
    Private Sub cboColumnNames2_AfterUpdate()
        On Error GoTo cboColumnNames2_Err
        
        Dim strValues As String
        Dim rstStudents As ADODB.Recordset
        Dim conStudents As ADODB.Connection
        Dim iCounter As Integer
        
        Set rstStudents = New ADODB.Recordset
        Set conStudents = CurrentProject.Connection
        
        rstStudents.Open "SELECT " & [cboColumnNames2] & " FROM Students", conStudents, adOpenKeyset, adLockOptimistic
        
        ' If the user selects a MI, display each letter of the alphabet
        ' in the Values combo box
        If cboColumnNames2.Text = "MI" Then
            For iCounter = 65 To 90 Step 1
                strValues = strValues & Chr(iCounter) & ";"
                Me.cboValues.Visible = True
                Me.cboValues.Enabled = True
                Me.cboOperators.Enabled = True
                Me.txtRegularString.Visible = False
            Next
            
        ' If the user selects DOB, then hide the Values combo box
        ' and display the text box so the user can enter a date
        ElseIf cboColumnNames2.Text = "DOB" Then
            Me.cboValues.Visible = False
            Me.cboOperators.Enabled = True
            Me.txtRegularString.Visible = True
            Me.txtRegularString.Enabled = True
        
        ' If the user selects Gender, then
        ' enter only the available options
        ElseIf cboColumnNames2.Text = "Gender" Then
            strValues = "Female;Male;Unknown;"
            Me.cboValues.Visible = True
            Me.cboValues.Enabled = True
            Me.cboOperators.Enabled = True
            Me.txtRegularString.Visible = False
        
        ' Addresses are too long
        ElseIf cboColumnNames2.Text = "Address" Then
            Me.cboOperators.Enabled = False
            Me.cboValues.Enabled = False
            Me.txtRegularString.Enabled = False
    
        ' If the user selects State, then
        ' enter only the state close to Washington, DC
        ElseIf cboColumnNames2.Text = "State" Then
            strValues = "DC;MD;VA;WV;"
            Me.cboValues.Visible = True
            Me.cboValues.Enabled = True
            Me.cboOperators.Enabled = True
            Me.txtRegularString.Visible = False
            
        ' If the user selects ZIPCode, then hide the Values combo box
        ' and display the text box so the user can enter a ZIP Code
        ElseIf cboColumnNames2.Text = "ZIPCode" Then
            Me.cboValues.Visible = False
            Me.cboOperators.Enabled = True
            Me.txtRegularString.Visible = True
        
        ' For all the other text-based options
        Else
            Do
                strValues = strValues & rstStudents.Fields(cboColumnNames2.Text) & ";"
                rstStudents.MoveNext
            Loop While Not rstStudents.EOF
            
            Me.cboValues.Visible = True
            Me.cboValues.Enabled = True
            Me.cboOperators.Enabled = True
            Me.txtRegularString.Visible = False
        End If
        
        cboValues.RowSource = strValues
        
        Exit Sub
    
    cboColumnNames2_Err:
        MsgBox "There was an error when filtering the records." & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Description: " & Err.Description & vbCrLf & _
               "Please contact the program vendor if " & _
           "he is not sleeping at this time."
        Resume Next
    End Sub
  33. Return to the form and click the lower right button
  34. In the Events tab of the Properties window, double-click On Click and click its ellipsis button
  35. Implement it as follows:
     
    Private Sub cmdSumbmitFilter_Click()
    On Error GoTo cmdSumbmitFilter_Error
    
        Dim strFilter As String
        
        ' If the user selected DOB, then the filter should use # symbols
        If cboColumnNames2 = "DOB" Then
            strFilter = "" & cboColumnNames2 & " " & cboOperators & "#" & Me.txtRegularString & "#"
    
        Else
            strFilter = "" & cboColumnNames2 & " " & cboOperators & "'" & cboValues & "'"
        End If
        
        Me.Filter = strFilter
        Me.FilterOn = True
        
        Exit Sub
        
    cmdSumbmitFilter_Error:
        MsgBox "There was an error when filtering the records." & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Description: " & Err.Description & vbCrLf & _
               "Please contact the program vendor."
        Resume Next
    End Sub
  36. Return to Microsoft Access and switch the form to Form View
  37. Save the form
  38. Try filtering the records
  39. Close the form

Previous Copyright © 2005-2010 FunctionX, Inc. Next