Cycle Through Records in a Table using VBA

[RC-]

Hi all,
How can I cycle through all of the records in a table using VBA? Right
now, I have VB code that looks at the first record when the form opens but I
need to look at, and modify, all of the records in the table.

Any help will be great!!

 

[fredg]

Hi all,
How can I cycle through all of the records in a table using VBA? Right
now, I have VB code that looks at the first record when the form opens but I
need to look at, and modify, all of the records in the table.

Any help will be great!!

You can adapt this.
Add your own error handling.

Public Sub EditTable()
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Set Db = CurrentDb
Set rs = Db.OpenRecordset("YourTableName")

rs.MoveFirst
Do While Not rs.EOF
rs.Edit
If rs!LastName = "Jones" Then
With rs
rs![LastName] = "Johnston"
.Update
End With
End If
rs.MoveNext
Loop

rs.Close
Db.Close
Set rs = Nothing
Set Db = Nothing
End Sub

 

[RC-]

Perfect, that's what I was looking for!

Thanks much.
RC-

Hi all,
How can I cycle through all of the records in a table using VBA?
Right
now, I have VB code that looks at the first record when the form opens
but I
need to look at, and modify, all of the records in the table.

Any help will be great!!

You can adapt this.
Add your own error handling.

Public Sub EditTable()
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Set Db = CurrentDb
Set rs = Db.OpenRecordset("YourTableName")

rs.MoveFirst
Do While Not rs.EOF
rs.Edit
If rs!LastName = "Jones" Then
With rs
rs![LastName] = "Johnston"
.Update
End With
End If
rs.MoveNext
Loop

rs.Close
Db.Close
Set rs = Nothing
Set Db = Nothing
End Sub