Looping through a table/query

[Lee Johnson]

Gang,
Can someone offer up a bit of code to loop through a table/query and access
particular fields in Access?

Thanks very much
Lee Johnson
(e-mail address removed)

 

[John Vinson]

Gang,
Can someone offer up a bit of code to loop through a table/query and access
particular fields in Access?

Thanks very much
Lee Johnson
(e-mail address removed)

Well, it's rather uncommon that you would need to do this; most
operations that you'ld first jump to using looping can be done in a
Query.

If you do need to do so, use a Recordset. This can be done in either
the older DAO object model with which I'm more familiar, or the newer
(but equally obsolete, now that Microsoft is all gung-ho for .NET) ADO
model. Here's the DAO code - you'll need to select Tools... References
and check "Microsoft DAO x.xx Object Library", highest version, for
this to work.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset "query-or-table-name", dbOpenDynaset
Do Until rs.EOF
Debug.Print rs!thisfield
rs.Edit
rs!Thatfield = <some value>
rs.Update
rs.MoveNext
Loop

 

[Graham R Seach]

Lee,

Dim db As Database
Dim rs As DAO.Recordset
Dim iCtr As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("table or query name")

Do While Not rs.EOF
Debug.Print "Record " & iCtr
'The following lines demonstrate the different ways you
'can return the value of the SAME field.
Debug.Print " " & rs.Fields(0) 'The first column is always zero
Debug.Print " " & rs.Fields("myField")
Debug.Print " " & rs!myField
Debug.Print " " & rs![myField]
Debug.Print " " & rs!("myField")
Debug.Print " " & rs.Collect("myField") 'Undocumented & unsupported
Debug.Print " " & rs.Collect(0) 'Undocumented & unsupported

iCtr = iCtr + 1
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia