
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 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.
Comments are closed.