If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Updating all records in a recordset
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? |
#2
|
|||
|
|||
Updating all records in a recordset
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? . |
#3
|
|||
|
|||
Updating all records in a recordset
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 "BrianC" wrote in 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) 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? |
#4
|
|||
|
|||
Updating all records in a recordset
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 |
#5
|
|||
|
|||
Updating all records in a recordset
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) -----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) 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? . |
Thread Tools | |
Display Modes | |
|
|