Updating all records in a recordset

[BrianC]

The following code returns multiple records to the
recordset. How do I update each record in the recordset
with the value of lngNext?

Set MyDb = CurrentDb
Set qdf = MyDb.QueryDefs("QueryBlockAv")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset(dbOpenDynaset)
rs.Edit
rs![Booking ID] = lngNext
rs.Update
rs.Close
Set rs = Nothing

This I believe only updates the first record. Is there a
way of looping through each record in the set and also
when do I know that I've got to the end?

 

[Chris Nebinger]

My Code below:

Chris Nebinger

-----Original Message-----
The following code returns multiple records to the
recordset. How do I update each record in the recordset
with the value of lngNext?

Set MyDb = CurrentDb
Set qdf = MyDb.QueryDefs("QueryBlockAv")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset(dbOpenDynaset)

'*****************
Do Until rs.EOF
'*****************

rs.Edit
rs![Booking ID] = lngNext
rs.Update '*****************
rs.movenext
loop
'*****************

rs.Close
Set rs = Nothing

This I believe only updates the first record. Is there a
way of looping through each record in the set and also
when do I know that I've got to the end?
.

 

[lbrinkman]

You see check out the Access HELP files. Your "Next prm" needs to be
inserted after rs.Update. Otherwise, you are not looping through the
records. Also, you might want to close the database: Set Mydb = Nothing

For Each element In group
[statements]
[Exit For]
[statements]
Next [element]
---Phil Szlyk

 

[Marc]

Answers inline

The following code returns multiple records to the
recordset. How do I update each record in the recordset
with the value of lngNext?

Set MyDb = CurrentDb
Set qdf = MyDb.QueryDefs("QueryBlockAv")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset(dbOpenDynaset)

if rs.RecordCount = 0 then
' no records processing
goto eventExit
end if

while rs.EOF = false

rs.Edit <== never actually used this command, not really necessary
rs![Booking ID] = lngNext
rs.Update rs.MoveNext
wend
rs.Close
Set rs = Nothing

This I believe only updates the first record. Is there a
way of looping through each record in the set and also
when do I know that I've got to the end?

HTH
Marc

 

[VanT.Dinh]

OTOH, if you update a large number of Records, it is more
efficient to use:

[CurrentDatabaseObject].Execute [Update Query/SQL String]

rather than traversing the Recordset one Record/Rw at a
time.

HTH
Van T. Dinh
MVP (Access)