Updating all records in a recordset

  • Thread starter Thread starter BrianC
  • Start date Start date
B

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?
 
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)
Click to expand...
'*****************
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?
.
Click to expand...
 
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
 
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)
Click to expand...
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?
Click to expand...

HTH
Marc
 
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)
 
Back
Top