Ms Access Runtime Error 3020
edit + Ask a Question Need help? Post your question and get tips & solutions from a community of 418,595 IT Pros & Developers. It's quick & easy. Run-time error 3020; Update or CancelUpdate without AddNew or Edit P: 49 AndyB2 Using Access 2010 Windows 7 I'm sure this is a simple VBA Gramer problem. I am attempting to update a table. Below is the code and I'm getting the error on the line BOM("Level") = 1, Line 21. I'm sure lines 22, 25, and 27 will have the same issue. Expand|Select|Wrap|Line Numbers PrivateSubCB_RunQueryPrintReport_Click() DimstDocNameAsString DimFilenameAsString Dimi,RC,XAsInteger DimMyDBAsDAO.Database DimREQ,BOMAsDAO.Recordset DimUDIAsDAO.Recordset2 'DimBOMAsDAO.Recordset DimCurrSpeciesAsString SetMyDB=DBEngine.Workspaces(0).Databases(0) SetREQ=MyDB.OpenRecordset("Tbl_REQData",DB_OPEN_TABLE) SetUDI=MyDB.OpenRecordset("Tbl_UDIData",DB_OPEN_TABLE) SetBOM=MyDB.OpenRecordset("Tbl_BOM_Level",DB_OPEN_TABLE) DoCmd.HourglassTrue DoCmd.SetWarningsFalse i=0 BOM("Level")=i BOM("PrevLevel")=i DoCmd.OpenQuery"Qry_FindPanelParts" Fori=1To7 BOM("Level")=i DoCmd.OpenQuery"Qry_FindPanelPartsNextLevelDown" BOM("PrevLevel")=i Nexti etc...errorisabove: Jan 7 '13 #1 Post Reply ✓ answered by Seth Schrock In order to edit a recordsource in VBA, you must use the Edit method (as the error states). So what you need is something like: Expand|Select|Wrap|Line Numbers WithBOM .Edit !Level=i !PrevLevel=i .Update EndWith This replaces lines 21, 22, 25, & 27. Here is a link that explains the Edit method: Recordset.Edit Method Share this Question 1 Reply Expert 2.5K+ P: 2,778 Seth Schrock In order to edit a recordsource in VBA, you must use the Edit method (as the error states). So what you need is something like: Expand|Select|Wrap|Line Numbers WithBOM .Edit !Level=i !PrevLevel=i .Update EndWith This replaces lines 21, 22, 25, & 27. H
Community Links Social Groups Pictures & Albums Members List Calendar Search Forums Show Threads Show Posts Tag Search Advanced Search Find All Thanked Posts Go to Page... Thread Tools Rating: Display Modes 10-28-2008, 10:47 AM #1 bbulla I'd rather be golfing Join Date: Feb 2005 Location: Oshawa, Ontario Posts: 95 Thanks: 9 Thanked 0 Times in 0 Posts Run Time error: 3020 when editing recordSet Hi, I get an 'Update or CancelUpdate without AddNew or Edit' error when running the following code. (the https://bytes.com/topic/access/answers/945792-run-time-error-3020-update-cancelupdate-without-addnew-edit error happens where I have placed the **) The query has two fields: Phone and Fax, and in the table they are the proper format/length for this to work. When i do a debug and hover over the variables on the marked line, everything looks as expected. I cannot figure this error out. Public Sub Add_Dashes() Set dbs = CurrentDb Set rs http://www.access-programmers.co.uk/forums/showthread.php?t=159585 = dbs.OpenRecordset("qryPhoneFax") Dim x As Integer Dim y As Integer Dim tempPhone As String Dim tempFax As String x = 0 y = 0 rs.Edit Do Until rs.EOF If Len(rs!phone) = 10 Then tempPhone = dash_it(rs!phone) rs!phone = tempPhone '** x = x + 1 End If If Len(rs!fax) = 10 Then rs!fax = dash_it(rs!fax) y = y + 1 End If rs.MoveNext Loop rs.Update rs.Close rs = Nothing MsgBox (x & " Phone Numbers changed : " & y & " Fax Numbers changed") End Sub Private Function dash_it(number As String) Dim first As String Dim second As String Dim third As String If Len(number) = 10 Then first = Left(number, 3) second = Left(Right(number, 7), 3) third = Right(number, 4) dash_it = first & "-" & second & "-" & third End If End Function bbulla View Public Profile Find More Posts by bbulla 10-28-2008, 10:59 AM #2 pbaldy Wino Moderator Join Date: Aug 2003 Location: Nevada, USA Posts: 28,922 Thanks: 6 Thanked 3,236 Times in 3,182 Posts Re: Run Time error: 3020 when editing recordS
for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Ask a Question Ask for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Expand Search Submit Close Search Login Join Today Products BackProducts Gigs Live Careers https://www.experts-exchange.com/questions/22807820/MS-Access-Run-time-error-3020-Update-or-Cancel-Update-without-Addnew-or-Edit.html Vendor Services Groups Website Testing Store Headlines Experts Exchange > Questions > MS Access Run-time error 3020, http://www.utteraccess.com/forum/Error-3020-Update-Canc-t430191.html Update or Cancel Update without Addnew or Edit Want to Advertise Here? Solved MS Access Run-time error 3020, Update or Cancel Update without Addnew or Edit Posted on 2007-09-05 MS Access 1 Verified Solution 9 Comments 4,674 Views Last Modified: 2013-11-28 I've been working on this piece of code and can't figure out why I'm receiving Run-time error '3020' Update or Cancel Update without AddNew or Edit. I'm just ms access trying to located a record id and allow the user to update the recordset. 'On Error GoTo Err_cmdFindRefId_Click Dim intRefId As Long Dim strBookMark As String intRefId = InputBox("Enter Reference ID to Locate", "Reference ID Search") strBookMark = Me.Bookmark 'Locate and display records based on Chargeback record number With Me.RecordsetClone .FindFirst "[ChargeBackRecordId] = " & intRefId If .NoMatch Then MsgBox "That Reference ID was not Located, " ms access runtime & vbCrLf & "Please try your entry again>" _ , vbExclamation, "Reference Id Search" Me.cmdFind.SetFocus Else Me.Bookmark = .Bookmark Me.IssueDate.SetFocus End If End With Exit_cmdFindRefId_Click: Exit Sub Err_cmdFindRefId_Click: MsgBox Err.Description Resume Exit_cmdFindRefId_Click End Sub 0 Question by:jumpy262000 Facebook Twitter LinkedIn Google LVL 77 Best Solution bypeter57r me.dirty = false will save the record; add to the start of your button code. Go to Solution 9 Comments LVL 77 Overall: Level 77 MS Access 67 Message Expert Comment by:peter57r2007-09-05 Are you changing but not saving the record that was visible before the search tried to move you to a different one? 0 LVL 1 Overall: Level 1 MS Access 1 Message Author Comment by:jumpy2620002007-09-05 Yes, I'm searching for a record, making a change and then searching again for a new record. 0 LVL 77 Overall: Level 77 MS Access 67 Message Expert Comment by:peter57r2007-09-05 Then that is why you are getting the eror- you have not saved the ciurrent record. 0 LVL 1 Overall: Level 1 MS Access 1 Message Author Comment by:jumpy2620002007-09-05 Would I have to create a new code or can I make it save when the button is press again? 0 LVL 77 Overall: Level 77 MS Access 67 Message Accepted Solution by:peter57r2007-09-05 me.d
Custom Search UtterAccess Forums>Microsoft® Access>Access Date + Time Error 3020 - Update or CancelUpdate without AddNew or Edit. Forum HomeSearchHelpUA Messages|-- UtterAccess.com NewsAccess Knowledge Center|-- Access Code Archive|-- Access Knowledgebase / FAQ|-- Access TutorialsMicrosoft® Access|-- Local Access User Groups (AUGs)|-- Interface Design|-- Access Q and A|-- Access Tables + Relationships|-- Access Queries|-- Access Forms|-- Access Reports|-- Access Macros|-- Access Modules|-- Access Date + Time|-- Access Errors + Error Handling|-- Access Built-in Functions|-- Access Searching + Data Mining|-- Access Records|-- Access Security|-- Access Automation|-- Access Database Container|-- Tool bars, Menu bars + Ribbon|-- Access Services (Web Databases)|-- Access Data Access Pages (Deprecated)|-- Access Data Projects - ADP|-- Access Runtime, Packaging & DeploymentAnd More...|-- Microsoft SQL Server|-- SQL Server Reporting Services|-- Microsoft SharePoint|-- Microsoft Internet Explorer|-- Visual Basic 6 and Earlier|-- Visual Basic 2003 and Later|-- ASP and VBScript|-- Web Development|-- PHP, Perl, MySQL and Postgres|-- Other Database Engines|-- BI Tools and Applications|-- JAVA and C#|-- GraphicsMicrosoft® Office|-- Microsoft Excel|-- Microsoft Excel FAQs|-- Microsoft Word|-- Microsoft Outlook|-- Microsoft PowerPoint|-- Microsoft FrontPage/Exp. Web|-- Other Microsoft ProductsPC|-- Network Issues|-- Q & A - Hardware|-- Q & A - Software|-- Q & A - Other PC|-- Virus + Security DiscussionUtterAccess Odds and Ends|-- General Chat|-- Q & A - UtterAccess.com Forums|-- Q & A - Access Wiki Options kountryView Member Profile Apr 6 2004, 12:22 AM Post#1Posts: 151Joine