By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,834 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

Recordset update problem

P: 30
I've written the following code to update a recordset but when I run it I get a Run-time error 3020: Update or CancelUpdate without AddNew or Edit. When I debug it highlights the rs.update line. I've serached this forum and others for an answer but no luck. Anyone got any ideas? Thanks

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Public Function fImportdata()
  4. Dim db As DAO.Database
  5. Dim rs As DAO.Recordset
  6. Set db = CurrentDb()
  7. Set rs = db.OpenRecordset("Import Query", dbOpenDynaset)
  8. With rs
  9. .MoveFirst
  10. .Edit
  11. While .EOF = False
  12.      If rs![M1] = "Y" And rs![M2] <> "Y" Then
  13.         rs![M2] = "Y" ' Will update M2 with a Y if M1=Y and M2 is null.
  14.         GoTo MyLabel
  15.     Else
  16.     End If
  17.     If rs![M2] = "Y" And rs![M3] <> "Y" Then
  18.         rs![M3] = "Y" 'Will update M3 with a Y if M1=Y and M2 is null.
  19.         GoTo MyLabel
  20.     Else
  21.     End If
  22.     If rs![M3] = "Y" And rs![M4] <> "Y" Then
  23.         rs![M4] = "Y"
  24.         GoTo MyLabel
  25.     Else
  26.     End If
  27.     If rs![M4] = "Y" And rs![M5] <> "Y" Then
  28.         rs![M5] = "Y"
  29.         GoTo MyLabel
  30.  
  31.     End If
  32. MyLabel:
  33.     rs.Update
  34.     rs.MoveNext
  35. Wend
  36. End With
  37. rs.Close
  38. End Function
Jan 24 '08 #1
Share this Question
Share on Google+
5 Replies


debasisdas
Expert 5K+
P: 8,127
before assigning the value to recordset use rs.edit
Jan 24 '08 #2

P: 30
before assigning the value to recordset use rs.edit
I have put .edit in line 10. Is this incorrect?
Jan 24 '08 #3

daniel aristidou
100+
P: 491
Im noy sure which version vb ure usin form the code
But try adding an end edit command just before update..
Jan 24 '08 #4

P: 30
Im noy sure which version vb ure usin form the code
But try adding an end edit command just before update..
Thanks for your help.
It's version 6.3
It doesn't recognise the EndEdit command although I may not have done it correctly. How would you put that command in?
Jan 24 '08 #5

P: 30
Im noy sure which version vb ure usin form the code
But try adding an end edit command just before update..
Hi I've managed to fix it and all running smoothly. Thanks for your help.
Jan 24 '08 #6

Post your reply

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