Typical DAO.Recordset VBA for looping through and altering

Function TypicalDAOrecordset()

'Make sure the name of the recordset is unambiguous
'Good practice to reference the actual library

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM T001Main where T001Main.ValueNumber = 0")

'the data source can be a Table Name a query name or an sql string
'it would be possible to change the SQL to set to another set of records
'Check to see if there are any records in the set

If Not (rs.EOF And rs.BOF) Then
'there are no records if End of File and beginning of file are both true

rs.MoveFirst

Do Until rs.EOF = True
rs.Edit
rs!ValueNumber = 300
rs.Update
rs.MoveNext
Loop
Else
MsgBox "No Records available for updating exit sub"
Exit Function
End If
MsgBox "Looped through the records and updated ValueNumber field"

rs.Close
Set rs = Nothing
Set db = Nothing

'libraries for DAO can be found on AllenBrowne site
'remember to break an infinite loop press ctrl + break

End Function

About Mark

Mark Brooks a forty something individual working and living in and around Edinburgh
This entry was posted in All, MS Access, VBA Code MS Access. Bookmark the permalink.