Access VBA Recordsets – Open, Count, Loop and More

Opening a Recordset

We must first identify the database we intend to use, which in this case is the currently open database. We can then open/create our Recordset using the CurrentDB.OpenRecordSet method.

To create a Recordset that will allow us to manipulate the data in the ProductName table, we would use the following code:

CurrentDb.OpenRecordset ("ProductName")

Counting the number of Records using VBA

After you've created a Recordset, you're probably going to want to do something useful with it or manipulate the data in it in some way. You can use the following code to count the number of records in your dataset (in this case, the ProductName table):

MsgBox CurrentDb.OpenRecordset("ProductName").RecordCount

Looping through a RecordSet using VBA

The following code loops through our RecordSet:

Sub RecordSet_Loop ()
Dim ourDatabase As Database
Dim ourRecordset As Recordset
Set ourDatabase = CurrentDb
Set ourRecordset = ourDatabase.OpenRecordset("ProductName")
Do Until ourRecordset.EOF
MsgBox ourRecordset!ProductID
ourRecordset.MoveNext
Loop
End Sub

Deleting a Record from a Recordset

To delete a record from a Recordset, first use the Recordset to make it the current record. The method FindFirst. The Recordset can then be used to delete it. Remove method. The code below demonstrates how to delete record 2 from the data set:

Sub RecordSet_DeleteRecord ()
Dim ourDatabase As Database
Dim ourRecordset As Recordset
Set ourDatabase = CurrentDb
Set ourRecordset = ourDatabase.OpenRecordset("ProductName", Type:=RecordsetTypeEnum.dbOpenDynaset)
With ourRecordset
.FindFirst "ProductName = "   "'Product Book1'"
If .NoMatch Then
MsgBox "No Match Found"
Else
ourRecordset.Delete
End If
End With
'Re-open Table
DoCmd.Close acTable, "ProductName"
DoCmd.OpenTable "ProductName"
End Sub

Design by Vannak Vannaro