How to loop through all records in the table?

[sabavno]

Hi,

I need to loop through all records in the table in order to assign the value to the field

Please suggest the code

Thanks

 

[Russie]

Hi

It's quicker to update in code using sql and if I were to loop through a recordset I would use the 'with' construct.

The basic code is:

Public Sub loopThroughARecordset()

Dim db As Database
Dim myRecordSet As Recordset
Dim var1, var2 as String

Set db = CurrentDb()
Set myRecordSet = db.OpenRecordset("my table or query&quot

If myRecordSet.BOF = True Then Exit Sub

Do Until myRecordSet.EOF
var1 = myRecordSet!value1
var2 = myRecordSet!value2

myRecordSet.Movenext

Loop

myRecordSet.Close
Set myRecordSet = Nothing

db.Close
Set db = Nothing

End Sub

 

[GrantDB]

This is taken from my own code, so there maybe a bit too much info, so apologies for that. It simply gets a record, gets a field (Fields(1)), then changes that data, then updates the field. The code then moves to the next record

Let me know if it helps

Grant



Dim rstSource As Recordset
Dim SQLStatement As String
Dim inputText As String
Dim outputText As String
Dim currentID As Integer
'select statement to get recordset to change
'must have two fields, a unique ID and the field to change
SQLStatement = "SELECT ProductID, ProductName FROM Products"
Set dbsCurrent = CurrentDb

Set rstSource = dbsCurrent.OpenRecordset(SQLStatement, dbOpenSnapshot)

'go through each record
With rstSource
If .RecordCount Then
.MoveFirst
Do Until .EOF
currentID = .Fields(0).Value
inputText = .Fields(1).Value

Debug.Print inputText
'new text for selected record field
outputText = "newText"



'make nice sql
outputText = Replace(outputText, "'", "''&quot
'update record based on unique ID
SQLUpdateStatement = "UPDATE Products SET ProductName = '" & outputText & "' WHERE ProductID=" & currentID
dbsCurrent.Execute SQLUpdateStatement
.MoveNext

Loop
End If
End With
rstSource.Close
Set rstSource = Nothing
Set dbsCurrent = Nothing