Looping Through a Recordset

In this blog post we will be demonstrating how to loop through a recordset.

Recordsets act like a cursor or a ruler underneath a row of data.  They only operate on one row at a time so to access the data returned by the database we must Move the cursor Next or Previous, First or Last.

Please download Looping Through A Recordset

The EOF and BOF properties

Recordsets have two important properties when looping through data, EOF (End-Of-File) and BOF (Beginning-Of-File). Recordsets are like tables and when you loop through one, you are literally moving from record to record in sequence. As you move through the records the EOF property is set to false but after you try and go past the last record, the EOF property becomes true. This works the same in reverse for the BOF property.

These properties let us know when we have reached the limits of a recordset.

The Code

Sub DAOLooping()
On Error GoTo ErrorHandler

Dim strSQL As String
Dim rs As DAO.Recordset

strSQL = "tblTeachers"
'For the purposes of this post, we are simply going to make 
'strSQL equal to tblTeachers.
'You could use a full SELECT statement such as:
'SELECT * FROM tblTeachers (this would produce the same result in fact).
'You could also add a Where clause to filter which records are returned:
'SELECT * FROM tblTeachers Where ZIPPostal = '98052'
' (this would return 5 records)

Set rs = CurrentDb.OpenRecordset(strSQL)
'This line of code instantiates the recordset object!!! 
'In English, this means that we have opened up a recordset 
'and can access its values using the rs variable.

With rs

    
    If Not .BOF And Not .EOF Then
    'We don’t know if the recordset has any records, 
    'so we use this line of code to check. If there are no records 
    'we won’t execute any code in the if..end if statement.    
        
        .MoveLast
        .MoveFirst
        'It is not necessary to move to the last record and then back 
        'to the first one but it is good practice to do so.
        
        While (Not .EOF)
        'With this code, we are using a while loop to loop 
        'through the records. If we reach the end of the recordset, .EOF 
        'will return true and we will exit the while loop.
            
            Debug.Print rs.Fields("teacherID") & " " & rs.Fields("FirstName")
            'prints info from fields to the immediate window

            .MoveNext
            'We need to ensure that we use .MoveNext, 
            'otherwise we will be stuck in a loop forever… 
            '(or at least until you press CTRL+Break)
        Wend
        
    End If
    
    .close
    'Make sure you close the recordset...
End With

ExitSub:
    Set rs = Nothing
    '..and set it to nothing
    Exit Sub
ErrorHandler:
    Resume ExitSub
End Sub

Running the code above produces this result in the immediate window:

DAO Records
1 Anna
2 Antonio
3 Thomas
4 Christina
5 Martin
6 Francisco
7 Ming-Yang
8 Elizabeth
9 Sven

ADODB Recordsets

Here is the code from above set to work with an ADODB recordset. Make sure you set your references to the Microsoft Active X library…

Sub ADOLooping()

On Error GoTo ErrorHandler

Dim strSQL As String
Dim rs As New ADODB.Recordset

'we will be opening tblTeachers
strSQL = "tblTeachers"

rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

With rs

    'Ensure recordset is populated
    If Not .BOF And Not .EOF Then
        
        'not necessary but good practice
        .MoveLast
        .MoveFirst
        
        While (Not .EOF)
            'print info from fields to the immediate window
            Debug.Print rs.Fields("teacherID") & " " & rs.Fields("FirstName")
            .MoveNext
        Wend
        
    End If
    .close
End With

ExitSub:
    Set rs = Nothing
    Exit Sub
ErrorHandler:
    Resume ExitSub
End Sub

Related Posts

DoEvents
Loops – Do Until / Do While / Do
Loops – For Each
Loops – For Next
Loops – While Wend
Nested Loops
What are Recordsets
Updating, Adding And Deleting Records In a Recordset

As always, a site wouldn't be anything without its users so please feel free to comment! We welcome any comments you have on the material and any suggestions you may have for future content.


In our humble opinion, it is worth commenting just so you can play with Fun Captcha below!

4 Comments

  • Superbly explained, Thanks

  • Robert Austin says:

    Yes, absolutely.

  • Carol Pruett says:

    Can you populate an array with fields of a recordset? I need to do a running total based criteria in a recordset. Thanks! Carol Pruett

    • René Hug says:

      Yes, you can! I use it alot and it is quite handy when you want to fill values to a drop-down list, like in this example.

      Here some snippets.
      ….
      Dim arrResult() As Variant

      ‘***************************************************************************************************
      ‘ Your selection
      ‘***************************************************************************************************
      strSQL = “;”
      arrResult = Application.WorksheetFunction.Transpose(AdoRecordsetToArray(strSQL) ‘Depending on how many dimensions your array returns you have to transpose it
      With Me.dropdown
      .Clear
      .list = arrResult
      .AddItem C_UNKNOWN, 0
      .ListIndex = 0
      End With

      ‘—————————————————————————————
      ‘ Procedure : AdoRecordsetToArray
      ‘ DateTime : 15.06.2010 19:04
      ‘ Author : myself
      ‘ Purpose : Read data from database and return result array
      ‘—————————————————————————————

      Private Function AdoRecordsetToArray(sCommand) As Variant()

      ‘Declaration
      Const cstrProcedure = “AdoRecordsetToArray”
      Dim vRst As Variant
      Dim lCount As Long
      Dim lColumns As Long
      Dim lColumn As Long
      Dim lRow As Long
      Dim i As Integer

      ‘Initialization
      On Error GoTo hError

      Set rst = New ADODB.Recordset
      With rst
      ‘ Open query recordset object
      .Open Source:=sCommand, _
      ActiveConnection:=cnn, _
      CursorType:=adOpenKeyset, _
      Options:=adCmdText

      ‘If data is available..
      If Not .BOF And Not .EOF Then
      ‘..count returned records
      lCount = .RecordCount
      ‘..copy recordset to array
      vRst = .GetRows(lCount)
      Else
      ‘..or create a dummy array when there is no data
      vRst = Array(“No data”)
      End If ‘ Not .BOF And Not .EOF
      End With ‘ rst

      ‘Return array
      AdoRecordsetToArray = vRst

      HandleExit:
      On Error GoTo 0
      ‘Clean-up
      If Not rst Is Nothing Then rst.Close: Set rst = Nothing
      Erase vRst
      Exit Function

      hError:
      ‘your error handler here

      End Function

2 Trackbacks