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

Edit, Delete, Update ... Error :-(

P: n/a
Hello People,

Using MS Access 2003 VBA I get the error 3020 Update or CancelUpdate
without AddNew or Edit when I run through the following code. Can
anyone help suggest anything to try? Thanks.

On Error GoTo delete_failed

Dim RS_DEL As DAO.Recordset

Dim DB As DAO.Database

Set DB = CurrentDb

' Delete Row in Nodes_T table

Set RS_DEL = DB.OpenRecordset("SELECT * FROM Nodes_T WHERE [Record_ID]
=" & Me.Record_ID, dbOpenDynaset, dbSeeChanges)

If RS_DEL.RecordCount = 1 Then

' If found record with correct record ID, DELETE IT

RS_DEL.Edit
RS_DEL.Delete
RS_DEL.Update

RS_DEL.Close
DB.Close
Set RS_DEL = Nothing

End if
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
You don't need the .Edit and .Update, because you don't need the buffer
space for entering/changing a record when deleting.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Stephen" <bu*******@hotmail.com> wrote in message
news:5c**************************@posting.google.c om...
Hello People,

Using MS Access 2003 VBA I get the error 3020 Update or CancelUpdate
without AddNew or Edit when I run through the following code. Can
anyone help suggest anything to try? Thanks.

On Error GoTo delete_failed

Dim RS_DEL As DAO.Recordset

Dim DB As DAO.Database

Set DB = CurrentDb

' Delete Row in Nodes_T table

Set RS_DEL = DB.OpenRecordset("SELECT * FROM Nodes_T WHERE [Record_ID]
=" & Me.Record_ID, dbOpenDynaset, dbSeeChanges)

If RS_DEL.RecordCount = 1 Then

' If found record with correct record ID, DELETE IT

RS_DEL.Edit
RS_DEL.Delete
RS_DEL.Update

RS_DEL.Close
DB.Close
Set RS_DEL = Nothing

End if

Nov 13 '05 #2

P: n/a
Hi Stephen,

Doesn't it drive you crazy when Access throws you an error message saying
it's missing something, but you can plainly see that what it says is
"missing" is right there in front of you?

IMHO, the problem is that there is no "current record", even though your
recodset should only contain one record, correct?
It looks as though you have created a recordset, but have not "populated
it"...
Try:
*************************************
....

With RS_DEL
.MoveLast
.MoveFirst
.Delete
.Close
End With
....
*************************************
If this doesn't work, (or alternatively) you could just design a Delete
Query or SQL within this code that would do this just as well.
(Warning ...untested "aircode")

*************************************
....
Dim DB As DAO.Database
Set DB = CurrentDb

Dim MySQL As String
Dim Msg As String
Dim strDelete As String
strDeleted = Me![RecordID]

' Delete Row in Nodes_T table
MySQL = ""
MySQL = MySQL & "DELETE * FROM Nodes_T "
MySQL = MySQL & "WHERE ([Record_ID] = "
MySQL = MySQL & strDelete
MySQL = MySQL & ");"

' If found record with correct record ID, DELETE IT
DB.Execute MySQL,dbFailOnError

'Include a confirmation as the Execute method displays no warnings or
confirmation.
Msg = ""
Msg = Msg & "RecordID: "
Msg = Msg & strDelete
Msg = Msg & " has been successfully deleted."

MsgBox(Msg)

Set DB = Nothing
....
*************************************
--
HTH,
Don
=============================
Use My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================
"Stephen" <bu*******@hotmail.com> wrote in message
news:5c**************************@posting.google.c om...
Hello People,

Using MS Access 2003 VBA I get the error 3020 Update or CancelUpdate
without AddNew or Edit when I run through the following code. Can
anyone help suggest anything to try? Thanks.

On Error GoTo delete_failed

Dim RS_DEL As DAO.Recordset

Dim DB As DAO.Database

Set DB = CurrentDb

' Delete Row in Nodes_T table

Set RS_DEL = DB.OpenRecordset("SELECT * FROM Nodes_T WHERE [Record_ID]
=" & Me.Record_ID, dbOpenDynaset, dbSeeChanges)

If RS_DEL.RecordCount = 1 Then

' If found record with correct record ID, DELETE IT

RS_DEL.Edit
RS_DEL.Delete
RS_DEL.Update

RS_DEL.Close
DB.Close
Set RS_DEL = Nothing

End if

Nov 13 '05 #3

P: n/a
Good Suggestions although they didn't work :-(

First one gives same answer & second one isn;t quite syntax correct
but gives the error 'Invalid Operation'

I did a slight modification of your second suggestion:

Set RS_DEL = DB.OpenRecordset("DELETE FROM Nodes_T WHERE [Record_ID]
=" & Me.Record_ID, dbOpenDynaset, dbSeeChanges)

This is fustrating as it should work which must mean that I'm missing
something really simple here.
Nov 13 '05 #4

P: n/a
Delete queries don't create recordsets.

Note how Don called the query in his example:

DB.Execute MySQL,dbFailOnError
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Stephen" <bu*******@hotmail.com> wrote in message
news:5c**************************@posting.google.c om...
Good Suggestions although they didn't work :-(

First one gives same answer & second one isn;t quite syntax correct
but gives the error 'Invalid Operation'

I did a slight modification of your second suggestion:

Set RS_DEL = DB.OpenRecordset("DELETE FROM Nodes_T WHERE [Record_ID]
=" & Me.Record_ID, dbOpenDynaset, dbSeeChanges)

This is fustrating as it should work which must mean that I'm missing
something really simple here.

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.