Access VBA Loop

MS Access VBA – Looping through records

Sub LoopRecExample()
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim iCount As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset("TableName") 'open the recordset for use (table, Query, SQL Statement)

With rs
If .RecordCount <> 0 Then 'Ensure that there are actually records to work with
'The next 2 line will determine the number of returned records
rs.MoveLast 'This is required otherwise you may not get the right count
iCount = rs.RecordCount 'Determine the number of returned records

Do While Not .BOF
'Do something with the recordset/Your Code Goes Here
End If
End With

rs.Close 'Close the recordset

On Error Resume Next
'Cleanup after ourselves
Set rs = Nothing
Set db = Nothing
Exit Sub

MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: LoopRecExample" & vbCrLf & "Error Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Sub

Leave a Reply

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