How to loop through a recordset and update the field of a recordset or delete current record

  • Thread starter Thread starter Karen Middleton
  • Start date Start date
K

Karen Middleton

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
 
Karen Middleton said:
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
Click to expand...

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.
 
Back
Top