How to Loop Through Recordset in MS Access

The following VBA example below shows you how to loop through a recordset in Microsoft Access.

Private Sub btn_recordset_loop_Click()
'**********************************************************************
'
'   Recordset Loop Example
'
'**********************************************************************

    '   Declare variables
    Dim rst As DAO.Recordset
    Dim dbs As DAO.Database
    Dim qry_string_1 As String
    Dim qry_string_2 As String
    Dim state As String
    Dim first_name As String
    Dim last_name As String

    '   Select records from the personnel table
    qry_string_1 = "SELECT * FROM personnel;"

    '   Set database and recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(qry_string_1, dbOpenSnapshot, dbReadOnly)

    '   Delete any existing records from the output table
    DoCmd.SetWarnings False 'Turn off warnings so we don't get popup
    dbs.Execute "DELETE * FROM output", dbFailOnError
    DoCmd.SetWarnings True 'Turn warnings back on

    '   If there are records in recordset
    If rst.RecordCount > 0 Then
        
        '   Move to the first record in recordset
        rst.MoveFirst
        
            '   Loop until end of file
            Do Until rst.EOF = True
                
                '   Assign recordset values to variables
                first_name = rst![first_name]
                last_name = rst![last_name]
                state = rst![state]
                
                '   Only write record to output table if state = "TX"
                If state = "TX" Then
                
                    '   Build insert query string
                    qry_string_2 = "INSERT INTO [output] ([first_name], " & _
                                   "[last_name], [state]) VALUES " & _
                                   "('" & first_name & "', '" & last_name & "'" & _
                                   ", '" & state & "');"
                    
                    '   Execute query
                    dbs.Execute qry_string_2, dbFailOnError
                
                End If
                
                '   Move to the next recordset
                rst.MoveNext
                
            Loop
            
    End If

    '   Cleanup
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

    '   Open the table in normal view as read-only data
    DoCmd.OpenTable "output", acViewNormal, acReadOnly

End Sub

In this example, we are going to loop through a set of personnel records. Every time we find a record where the person is from Texas, we will write this record to the output table. Finally, after we finish looping through all of the records, we will open the output table to view the results.

How to Implement this Recordset Loop Example?

To get started, I created an Access database with a personnel table, an output table, and a form with a button on it as shown below.

The personnel table contains 30 rows of dummy data including ID, First Name, Last Name, Street Address, City, and State fields.

The output table is empty and contains fields for First Name, Last Name, and State.

To add the VBA code to the button so it runs when the user clicks it, set the drop down arrow next to the On Click event to [Event Procedure] in the Property Sheet as shown below. Then click on the button with three dots to the right of the On Click event to open the VBA editor.

When the VBA editor opens, copy and paste the code from above. Save and close the editor.

Finally, let’s run the example. Click on the button on the form to run the recordset loop. Once it finishes running, the output table will open showing only records where the state is equal to “TX”.

And there you have it. We have looped through a recordset and have extracted only the data we want to separate table.