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
|
|||
|
|||
How to loop through a recordset and update the field of a recordset or delete current record
I want to read a table ActStage which as the following fields -
Customer, Store, Fiscalyearperiod & Sales all fields except Sales are text fields and Sales is a numeric field and sales can sometimes have null values. I want to loop through the recordset of ActStage table and I want to update some fields in the current recordset based on some complex business rules and similarly I want to delete the current record in the recordset if some rules are met. Please note I can do this in SQLServer T-SQL with what is called as a updateable cursor. I want to do the same in VBA on the recordset appreciate if you could share how it can be done. Please kindly share the code for the same. Thanks Karen Dim rstACTStage As DAO.Recordset Dim strSql As String Set rstACTStage = CurrentDb.OpenRecordset("select * from ACTStage") Do While rstACTStage.EOF = False ' Want to update Sales field in the ActStage table here ' Want to delete a record in the recordset based on some criteria rstACTStage.MoveNext Loop rstACTStage.Close Set rstACTStage = Nothing |
#2
|
|||
|
|||
"Karen Middleton" wrote in message
m... I want to read a table ActStage which as the following fields - Customer, Store, Fiscalyearperiod & Sales all fields except Sales are text fields and Sales is a numeric field and sales can sometimes have null values. I want to loop through the recordset of ActStage table and I want to update some fields in the current recordset based on some complex business rules and similarly I want to delete the current record in the recordset if some rules are met. Please note I can do this in SQLServer T-SQL with what is called as a updateable cursor. I want to do the same in VBA on the recordset appreciate if you could share how it can be done. Please kindly share the code for the same. Thanks Karen Dim rstACTStage As DAO.Recordset Dim strSql As String Set rstACTStage = CurrentDb.OpenRecordset("select * from ACTStage") Do While rstACTStage.EOF = False ' Want to update Sales field in the ActStage table here ' Want to delete a record in the recordset based on some criteria rstACTStage.MoveNext Loop rstACTStage.Close Set rstACTStage = Nothing To update the current record in the recordset: rstACTStage.Edit rstACTStage!SomeField = SomethingOrOther rstACTStage.Update To delete the current record in the recordset: rstACTStage.Delete Note that performing updates to a table by iterating through a recordset is inefficient and is generally a last resort. Most developers would try very hard to devise a way of performing the updates using queries. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to read a table record by record and process and update another table | Karen Middleton | Running & Setting Up Queries | 1 | January 3rd, 2005 12:30 PM |
How to read a table record by record and process and update another table | Karen Middleton | Running & Setting Up Queries | 1 | January 2nd, 2005 03:58 AM |
subform's field and corresponding recordset out of sync? | AB | General Discussion | 3 | November 8th, 2004 10:54 PM |
No Current Record error | Laurel | New Users | 5 | September 10th, 2004 06:50 PM |
Recordset in subform based on field in parent form | Lyn | General Discussion | 15 | June 14th, 2004 03:10 PM |