472,973 Members | 2,463 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,973 software developers and data experts.

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

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
4 9340
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Qnavry | last post by:
I am creating one page to edit, delete, and add data to a database. The Add code works but the Edit and Delete do not. What am I doing wrong? If you need more code let me know. Thanks Code:...
4
by: Smudger | last post by:
I've succesfully connected my MS Access Database to my .NET app and can view / navigate records. What I would like to do now though is perform add, edit & delete functions on these records is...
12
by: dennist685 | last post by:
Can't edit, delete or add row in an Access database in a website 2003 When I implement a walkthrough using Northwind I have no trouble doing this. Also, in a windowsforms project I have no...
0
by: kuldeep singh sethi | last post by:
Hi all I want to know that how to edit,Delete and cancel data in datagrid using c#. Any body would like to help me plz for this. kuldeep singh sethi
0
by: TT | last post by:
Hello. I'm working in asp.net and i need to disable the edit/delete functionality from a frmview component. help me.thang you! my ACL sub: Protected Sub Page_Load(ByVal sender As Object,...
3
by: ebru | last post by:
about insert/delete/update records in mysql server with asp code how can I insert record in mysql database.. I insert into database but if I leave one text box empty, its give me error how can...
2
by: =?Utf-8?B?SWJyYWhpbS4=?= | last post by:
Hello, I have a client c# application from which I want to Add/Edit/Delete & list records by connecting to Web Service. HOw can I write classes & methods in Web service projet that will : ...
0
by: justjazz | last post by:
I am successfully able to perform a direct sql/400 (in iseries) call to the delete statement DELETE FROM RDSUFAQA.SAPXFER but when I make a call to the same sql statement via a stored...
0
yawar
by: yawar | last post by:
Hi all, I am beginner in MySql Databases. I am using C#, DataGridView and Remote MySql database (MySql is located at my website) I am able to connect to my database and can show all the rows...
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.