422,767 Members | 1,136 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,767 IT Pros & Developers. It's quick & easy.

Updating table with recordSource - Error 3020

P: 22
Dear Community,

I am trying to update all entries from table "tbValve" when a specific criteria is verified (regarding Fabric value).
When implementing the code bellow, an error 3020 occurs (Update or CancelUpdate without AddNew or Edit).
Need help because I cannot understand why is this happening.
Thank you!!

Expand|Select|Wrap|Line Numbers
  1.         Dim rs As DAO.Recordset, query1 As String
  2.         query1 = "Select TagValve from tbValve Where Fabric = '" & cbEdit.Column(1) & "'"
  3.         Set rs = CurrentDb.OpenRecordset(query1)
  4.         With rs
  5.         If Not .BOF And Not .EOF Then 
  6.         .MoveFirst
  7.         .Edit
  8.         Do While Not rs.EOF
  9.         !TagValve = Replace(cbEdit.Column(0), cbEdit.Column(0), TagValve)
  10.         .MoveNext
  11.         Loop
  12.         .Update
  13.         .Close
  14.         End If
  15.         End With
  16.         Set rs = Nothing
Sep 12 '17 #1

✓ answered by Rabbit

You are only editing the first row and then updating the last row.

You have to call edit and update on each row. Move them inside the loop.

Aside from that, it's probably better to run an update query rather than looping through a recordset.

Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,204
You are only editing the first row and then updating the last row.

You have to call edit and update on each row. Move them inside the loop.

Aside from that, it's probably better to run an update query rather than looping through a recordset.
Sep 12 '17 #2

P: 22
Hello Rabbit,

Thank you for your answer.
Even inside the loop, the same error occurs.
I will try to implement the update query.
Ty

Regards
Sep 12 '17 #3

Rabbit
Expert Mod 10K+
P: 12,204
It would help to see where you put it in the revised code. The update has to be before the MoveNext. Otherwise you run into the same problem of editing one row but updating a different row.
Sep 12 '17 #4

P: 22
My inattention! You are absolutely right. The problem is now solved with your advice.
Thank you very much.

Best regards.
Sep 12 '17 #5

Post your reply

Sign in to post your reply or Sign up for a free account.