Access VBA loop through Table records

This Access VBA tutorial explains how to loop through Table records or Query records in Microsoft Access.

You may also want to read:

Access VBA loop through all Tables using DAO.TableDef

Access VBA loop through Table records

Suppose we have an Acess Table or Query and you want to loop through the records (in Excel terms, to loop the values in each row), we have to make use of Recordset objects, which has different Methods to help us manipulate data in a database at the record level.

In this tutorial, I will demonstrate a standard Procedure to loop through Table records.

VBA Procedure

Press ALT+F11, copy and paste the below in a new Module.

Sub loopTable()
    Dim strSQL As String
    Dim rs As DAO.Recordset
    
    strSQL = "SELECT * FROM employee_tbl"   'define the SQL result that you want to loop
    Set rs = CurrentDb.OpenRecordset(strSQL)
    
    If Not rs.BOF And Not rs.EOF Then
        rs.MoveFirst
        While (Not rs.EOF)
            Debug.Print rs.Fields("Employee Name") 'define the field you want to return data
            rs.MoveNext
        Wend
    End If
    rs.Close
    Set rs = Nothing
End Sub

To explain the key things:

  • BOF checks if current record position is before the first record
  • EOF checks if current record position is after the last record
  • If BOF and EOF both return TRUE, then the Table has no record
  • rs.MoveFirst ‘makes the first record the current record, in case current record is not the first record

VBA Procedure

Suppose employee_tbl contains the below information.

employee_tbl
Employee ID Employee Name Department Team Salary
001 David HR Compensation 10000
002 Mary Finance Reporting 20000
003 Peter HR HR System 30000
004 Apple HR Compensation 40000
005 June IT Operation 50000

Run the Procedure, the result is printed in Immediate box.

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *