Cycle Through Records in a Table using VBA

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!!

On Fri, 11 Feb 2005 10:11:26 -0700, RC- wrote:

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

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

Perfect, that's what I was looking for!

Thanks much.
RC-

"fredg" wrote in message
...
On Fri, 11 Feb 2005 10:11:26 -0700, RC- wrote:

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

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.