Hey guys,
'Update or CancelUpdate without AddNew or Edit'
On my database i keep occasionly get this error when i try and edit a field, it is not everytime. It will be working fine and then this error appears. I will be editing records and then a random one will get the error.
A bit of background on my form, this will seem a bit lengthy but here is my code.
The form has a navigation list which the user can select a record to view.
An Edit button which enables most of the fields on the form, as soon as the user goes to another record, the fields are not enabled.
2 fields lookup values from another table (related)
Thanks in advance guys. Any advice or tips greatly appreciated -
-
Option Compare Database
-
-
Private Sub cmdEditRecord_Click()
-
'When user clicks this button, Edit capabilities enable
-
-
' Trap for Errors
-
On Error Resume Next
-
-
Me.CSOC_Number.Locked = False
-
Me.Issue.Locked = False
-
Me.Title.Locked = False
-
Me.Comment.Locked = False
-
Me.Workflow_Status.Locked = False
-
Me.NHI_Delivery_Reference.Locked = False
-
Me.NHI_Delivery_Date.Locked = False
-
Me.AA_Delivery_Reference.Locked = False
-
Me.AA_Delivery_Date.Locked = False
-
Me.CoA_Response_Reference.Locked = False
-
Me.CoA_Response_Date.Locked = False
-
Me.NQO__Europe__CSOC_Approval.Locked = False
-
Me.NQO__Europe__CSOC_Approval_Date.Locked = False
-
Me.NQO__Europe_.Locked = False
-
-
Me.NQO__Europe__Approval_Reference.Locked = False
-
Me.NQO__Australia__Approval_Reference.Locked = False
-
Me.NQO__Australia_.Locked = False
-
-
Me.NQO__Australia__CSOC_Approval.Locked = False
-
Me.NQO__Australia__CSOC_Approval_Date.Locked = False
-
-
-
'Focus must be set to another control to avoid error when control is disabled
-
[lstCSOC].SetFocus
-
Me.cmdEditRecord.Enabled = False
-
Me.cmdDeleteRecord.Enabled = True
-
-
' Code to give the detail section a different colour when in view mode and edit mode
-
Me.Detail.BackColor = RGB(255, 255, 255)
-
-
End Sub
-
-
Private Sub Form_Current()
-
-
' Trap for Errors
-
On Error Resume Next
-
-
'Sets value selected in related list to Null every time the user changes record
-
Me![lstReqAndCSOC2] = Null
-
-
'Requery all the lists displaying relationships
-
'Updates every time the user changes records
-
Me!lstCSOC.Requery
-
Me!lstReqAndCSOC2.Requery
-
-
'Removes users ability to Add, Edit and Delete
-
'Exception is if user is on new form
-
Dim fStatus As Boolean
-
Dim nfStatus As Boolean
-
fStatus = Me.NewRecord
-
nfStatus = Not fStatus
-
-
Me.CSOC_Number.Locked = nfStatus
-
Me.Issue.Locked = nfStatus
-
Me.Title.Locked = nfStatus
-
Me.Comment.Locked = nfStatus
-
Me.Workflow_Status.Locked = nfStatus
-
Me.NHI_Delivery_Reference.Locked = nfStatus
-
Me.NHI_Delivery_Date.Locked = nfStatus
-
Me.AA_Delivery_Reference.Locked = nfStatus
-
Me.AA_Delivery_Date.Locked = nfStatus
-
Me.CoA_Response_Reference.Locked = nfStatus
-
Me.CoA_Response_Date.Locked = nfStatus
-
Me.NQO__Europe__CSOC_Approval.Locked = nfStatus
-
Me.NQO__Europe__CSOC_Approval_Date.Locked = nfStatus
-
Me.NQO__Europe_.Locked = nfStatus
-
Me.NQO__Europe__Approval_Reference.Locked = nfStatus
-
Me.NQO__Australia__Approval_Reference.Locked = nfStatus
-
Me.NQO__Australia_.Locked = nfStatus
-
Me.NQO__Australia__CSOC_Approval.Locked = nfStatus
-
Me.NQO__Australia__CSOC_Approval_Date.Locked = nfStatus
-
-
cmdEditRecord.Enabled = Not fStatus
-
cmdDeleteRecord.Enabled = fStatus
-
-
-
' If it is on a new Record
-
' Many of the functions cannot be performed on a new record, hence the need for the if statement
-
If (fStatus = True) Then
-
' Code to give the detail section a different colour when in view mode and edit mode
-
Me.Detail.BackColor = RGB(255, 255, 255)
-
Else:
-
Me.Detail.BackColor = RGB(211, 211, 211)
-
-
' This code automatically finds the NQO's for the Substantiation File from the requirements table
-
' variable used to retrieve data from the Requirements table
-
Dim var1, var2 As Variant
-
' Finds the Requirement for the current CSOC on the form
-
var1 = DLookup("[Requirement ID]", "tblReqAndCSOC", "Number =Forms![frmCSOC]![CSOC Number]")
-
' Updates the Relevant NQO for CSOC Approval (Europe) on the CSOC form using data from the Requirements table
-
var2 = DLookup("[Relevant NQO for CSOC Approval (Australia)]", "tblReq", "[Requirement ID]=" & "'" & var1 & "'")
-
Me.NQO__Australia_ = var2
-
' Updates the Relevant NQO for CSOC Approval (Europe) on the CSOC form using data from the Requirements table
-
var2 = DLookup("[Relevant NQO for CSOC Approval (Europe)]", "tblReq", "[Requirement ID]=" & "'" & var1 & "'")
-
Me.NQO__Europe_ = var2
-
-
End If
-
-
'Code to display date field to enter if CSOC has been approved
-
If ([NQO (Europe) CSOC Approval] = "1 - Approved") Then
-
Me.NQO__Europe__CSOC_Approval_Date.Enabled = True
-
Me.NQO__Europe__Approval_Reference.Enabled = True
-
Else: Me.NQO__Europe__CSOC_Approval_Date.Enabled = False
-
Me.NQO__Europe__Approval_Reference.Enabled = False
-
End If
-
-
'Code to display date field to enter if CSOC has been approved
-
If ([NQO (Australia) CSOC Approval] = "1 - Approved") Then
-
Me.NQO__Australia__CSOC_Approval_Date.Enabled = True
-
Me.NQO__Australia__Approval_Reference.Enabled = True
-
Else: Me.NQO__Australia__CSOC_Approval_Date.Enabled = False
-
Me.NQO__Australia__Approval_Reference.Enabled = False
-
End If
-
-
End Sub
-
-
Private Sub lstCSOC_AfterUpdate()
-
-
' Trap for Errors
-
On Error Resume Next
-
-
' Requrery all the related list after the navigation list has been changed
-
Me!lstReqAndCSOC2.Requery
-
-
' Find the record that matches the control.
-
Dim rs As Object
-
Set rs = Me.Recordset.Clone
-
rs.FindFirst "[ID] = " & Str(Nz(Me![lstCSOC], 0))
-
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
-
-
End Sub
-
-
-
-
'Code to display date field to enter if CSOC has been approved
-
Private Sub NQO__Australia__CSOC_Approval_Change()
-
If ([NQO (Australia) CSOC Approval] = "1 - Approved") Then
-
Me.NQO__Australia__CSOC_Approval_Date.Enabled = True
-
Me.NQO__Australia__Approval_Reference.Enabled = True
-
Else: Me.NQO__Australia__CSOC_Approval_Date.Enabled = False
-
Me.NQO__Australia__Approval_Reference.Enabled = False
-
Me.NQO__Australia__CSOC_Approval_Date = ""
-
Me.NQO__Australia__Approval_Reference = ""
-
End If
-
End Sub
-
-
-
'Code to display date field to enter if CSOC has been approved
-
Private Sub NQO__Europe__CSOC_Approval_Change()
-
If ([NQO (Europe) CSOC Approval] = "1 - Approved") Then
-
Me.NQO__Europe__CSOC_Approval_Date.Enabled = True
-
Me.NQO__Europe__Approval_Reference.Enabled = True
-
Else: Me.NQO__Europe__CSOC_Approval_Date.Enabled = False
-
Me.NQO__Europe__Approval_Reference.Enabled = False
-
Me.NQO__Europe__CSOC_Approval_Date = ""
-
Me.NQO__Europe__Approval_Reference = ""
-
End If
-
End Sub
-
-
-
-
Private Sub CSOC_Number_AfterUpdate()
-
-
' Trap for Errors
-
On Error Resume Next
-
-
' Form is refreshed
-
' Refreshing of form will only take place if Issue number and CSOC both contain data
-
If Not IsNull(Me.Issue) Then
-
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
-
End If
-
-
End Sub
-
-
Private Sub Issue_AfterUpdate()
-
-
' Trap for Errors
-
On Error Resume Next
-
-
' Form is refreshed
-
' Refreshing of form will only take place if Issue number and CSOC both contain data
-
If Not IsNull(Me.CSOC_Number) Then
-
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
-
End If
-
-
'After user has entered in the Issue, the alpha characters will be capitalised
-
Me.Issue = UCase(Me.Issue)
-
-
End Sub
-
-
Private Sub Form_Activate()
-
' The Activate() Event triggers whenever another window is closed and/or this window takes focus
-
-
' Trap for Errors
-
On Error Resume Next
-
-
' Refresh Form, Required so that lists show correct relationships
-
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
-
-
' If it is on a new Record
-
Dim fStatus As Boolean
-
fStatus = Me.NewRecord
-
' If it is on a new Record
-
' Many of the functions cannot be performed on a new record, hence the need for the if statement
-
If (fStatus = True) Then
-
Else:
-
-
' This code automatically finds the NQO's for the CSOC from the requirements table
-
' To variables used to retrieve data from the Requirements table
-
Dim var1, var2 As Variant
-
-
' Finds the Requirement for the current CSOC on the form
-
var1 = DLookup("[Requirement ID]", "tblReqAndCSOC", "Number =Forms![frmCSOC]![CSOC Number]")
-
-
' Updates the Relevant NQO for CSOC Approval (Europe) on the CSOC form using data from the Requirements table
-
var2 = DLookup("[Relevant NQO for CSOC Approval (Australia)]", "tblReq", "[Requirement ID]=" & "'" & var1 & "'")
-
Me.NQO__Australia_ = var2
-
-
' Updates the Relevant NQO for CSOC Approval (Europe) on the CSOC form using data from the Requirements table
-
var2 = DLookup("[Relevant NQO for CSOC Approval (Europe)]", "tblReq", "[Requirement ID]=" & "'" & var1 & "'")
-
Me.NQO__Europe_ = var2
-
-
End If
-
-
End Sub
9 7742 MMcCarthy 14,534
Recognized Expert Moderator MVP
I honestly can't tell from your code. When this error occurs what line is highlighted in yellow in the VBA editor?
Mary
The error message pops up with an OK and HELP button, so i dont get to see the code.
I have to delete the entry i just tried and exit the form to get it working again.
MMcCarthy 14,534
Recognized Expert Moderator MVP
The error message pops up with an OK and HELP button, so i dont get to see the code.
I have to delete the entry i just tried and exit the form to get it working again.
Is this error happening when you try to edit a particular field? The more information on how the error is triggered the better I'll be able to help.
Mary
Thanks for your help Mary.
It does not occur on any particular field or on any particular record. But I am now most certain it is occuring because of the code below -
-
If (fStatus = True) Then
-
Else:
-
-
' This code automatically finds the NQO's for the Substantiation File from the requirements table
-
' variable used to retrieve data from the Requirements table
-
Dim var1, var2 As Variant
-
' Finds the Requirement for the current CSOC on the form
-
var1 = DLookup("[Requirement ID]", "tblReqAndCSOC", "Number =Forms![frmCSOC]![CSOC Number]")
-
' Updates the Relevant NQO for CSOC Approval (Europe) on the CSOC form using data from the Requirements table
-
var2 = DLookup("[Relevant NQO for CSOC Approval (Australia)]", "tblReq", "[Requirement ID]=" & "'" & var1 & "'")
-
Me.NQO__Australia_ = var2
-
' Updates the Relevant NQO for CSOC Approval (Europe) on the CSOC form using data from the Requirements table
-
var2 = DLookup("[Relevant NQO for CSOC Approval (Europe)]", "tblReq", "[Requirement ID]=" & "'" & var1 & "'")
-
Me.NQO__Europe_ = var2
-
-
Which is in both the Form_Current() and Form_Activate
MMcCarthy 14,534
Recognized Expert Moderator MVP
Remove the colon at the end of Else. Does this make any difference?
MMcCarthy 14,534
Recognized Expert Moderator MVP
Remove the colon at the end of Else. Does this make any difference?
I can't find anything to indicate the error you are getting. Try testing the DLookup's in an unbound textbox on the form and see if they are working correctly.
Mary
The 2 fields that are being updated are locked = True, before the Dlookup updates them, i have now set made a change where before the Dlookup i set locked to false, then back to true after it has been updated.
It seems to be working so far, i havene't seen the error since i made that change, and i usually see it quite frequently.
Thanks Mary -
-
Private Sub Form_Current()
-
-
' Trap for Errors
-
On Error Resume Next
-
-
'Sets value selected in related list to Null every time the user changes record
-
Me![lstReqAndCSOC2] = Null
-
-
'Requery all the lists displaying relationships
-
'Updates every time the user changes records
-
Me!lstCSOC.Requery
-
Me!lstReqAndCSOC2.Requery
-
-
'Removes users ability to Add, Edit and Delete
-
'Exception is if user is on new form
-
Dim fStatus As Boolean
-
Dim nfStatus As Boolean
-
fStatus = Me.NewRecord
-
nfStatus = Not fStatus
-
-
Me.CSOC_Number.Locked = nfStatus
-
Me.Issue.Locked = nfStatus
-
Me.Title.Locked = nfStatus
-
Me.Comment.Locked = nfStatus
-
Me.Workflow_Status.Locked = nfStatus
-
Me.NHI_Delivery_Reference.Locked = nfStatus
-
Me.NHI_Delivery_Date.Locked = nfStatus
-
Me.AA_Delivery_Reference.Locked = nfStatus
-
Me.AA_Delivery_Date.Locked = nfStatus
-
Me.CoA_Response_Reference.Locked = nfStatus
-
Me.CoA_Response_Date.Locked = nfStatus
-
Me.NQO__Europe__CSOC_Approval.Locked = nfStatus
-
Me.NQO__Europe__CSOC_Approval_Date.Locked = nfStatus
-
Me.NQO__Europe_.Locked = nfStatus
-
Me.NQO__Europe__Approval_Reference.Locked = nfStatus
-
Me.NQO__Australia__Approval_Reference.Locked = nfStatus
-
Me.NQO__Australia_.Locked = nfStatus
-
Me.NQO__Australia__CSOC_Approval.Locked = nfStatus
-
Me.NQO__Australia__CSOC_Approval_Date.Locked = nfStatus
-
-
cmdEditRecord.Enabled = Not fStatus
-
cmdDeleteRecord.Enabled = fStatus
-
-
-
' If it is on a new Record
-
' Many of the functions cannot be performed on a new record, hence the need for the if statement
-
If (fStatus = True) Then
-
' Code to give the detail section a different colour when in view mode and edit mode
-
-
Else:
-
-
' This code automatically finds the NQO's for the Substantiation File from the requirements table
-
' variable used to retrieve data from the Requirements table
-
Dim var1, var2 As Variant
-
Me.NQO__Europe_.Locked = False
-
Me.NQO__Australia_.Locked = False
-
' Finds the Requirement for the current CSOC on the form
-
var1 = DLookup("[Requirement ID]", "tblReqAndCSOC", "Number =Forms![frmCSOC]![CSOC Number]")
-
' Updates the Relevant NQO for CSOC Approval (Europe) on the CSOC form using data from the Requirements table
-
var2 = DLookup("[Relevant NQO for CSOC Approval (Australia)]", "tblReq", "[Requirement ID]=" & "'" & var1 & "'")
-
Me.NQO__Australia_ = var2
-
' Updates the Relevant NQO for CSOC Approval (Europe) on the CSOC form using data from the Requirements table
-
var2 = DLookup("[Relevant NQO for CSOC Approval (Europe)]", "tblReq", "[Requirement ID]=" & "'" & var1 & "'")
-
Me.NQO__Europe_ = var2
-
Me.NQO__Europe_.Locked = True
-
Me.NQO__Australia_.Locked = True
-
End If
-
-
'Code to display date field to enter if CSOC has been approved
-
If ([NQO (Europe) CSOC Approval] = "1 - Approved") Then
-
Me.NQO__Europe__CSOC_Approval_Date.Enabled = True
-
Me.NQO__Europe__Approval_Reference.Enabled = True
-
Else: Me.NQO__Europe__CSOC_Approval_Date.Enabled = False
-
Me.NQO__Europe__Approval_Reference.Enabled = False
-
End If
-
-
'Code to display date field to enter if CSOC has been approved
-
If ([NQO (Australia) CSOC Approval] = "1 - Approved") Then
-
Me.NQO__Australia__CSOC_Approval_Date.Enabled = True
-
Me.NQO__Australia__Approval_Reference.Enabled = True
-
Else: Me.NQO__Australia__CSOC_Approval_Date.Enabled = False
-
Me.NQO__Australia__Approval_Reference.Enabled = False
-
End If
-
-
End Sub
-
-
MMcCarthy 14,534
Recognized Expert Moderator MVP
This is probably the correct solution. Locking controls on a form does prevent certain actions from being preformed.
Hope it continues to work.
Mary
NeoPa 32,566
Recognized Expert Moderator MVP
That's certainly true.
Updates are not possible on locked controls. The code you have worked out is the logical and correct answer so you should be good from now on. A concept to remember for future situations where you need to keep controls locked yet update them sometimes in the code.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: JSMiami |
last post by:
I have a form that has the navigation buttons enabled. I have a
procedure that runs every time a new record is loaded (Current Event).
This procedure performs a SELECT from a table (different from the one
that serves as a source to the form) so that I can prepopulate the
values of an unbound list box. I am getting this error:
Update or CancelUpdate without AddNew or Edit
Please help.
Thanks
|
by: NewDBGirl |
last post by:
I have a multiple-page form for entering info about a project with several
sub-forms for sub-projects, notes and tasks. The form and one of the
sub-forms each have a combo box to select a record to display, as well as
a "last date/time updated" type of field that populates AfterUpdate.
What can I do to fix the "Update or CancelUpdate without AddNew or Edit"
error? I'm pretty unschooled in code but I have gotten by with macros and...
|
by: Stephen |
last post by:
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
|
by: crispywafers |
last post by:
Hi,
Hope someone can help me out. I have a piece of code I use to update a
subforms numbers (Trimester Invoices) if the mainform numbers (School
Year Order) change.
What is interesting is the code works if I type numbers into the
mainform once, but if you accidently mistype something and go back
immediately to reenter the number into the same field you get this
"update or cancelupdate without addnew or edit" error.
|
by: DaveN |
last post by:
Hi all,
I'm trying to update a record in a table with data from text boxes on a
form. As a background to this, I managed to add a new record to the
table in a similar manner with the following:
Set db = CurrentDb
Set rs = db.OpenRecordset("Project Table")
rs.AddNew
rs("Project_Number") = Me!
| |
by: bbasberg |
last post by:
I have been working hard to clean up my code but I am still wondering why all incoming records go to the "AddNew" part of the IF statement and never to the Edit alternative. I believe that it must be because my rs.index never really receives the value from the cell in the spreadsheet that it should use to compare to the corresponding field in the Access DB. I have named the field "rtitle" in Access and made it an index called riskIndex. I am an...
|
by: anastasius |
last post by:
I have read other posts with a similar problem but I have not read of one like this.
When a user selects a name from the drop-down list called "cboLastName" (which is unbound and whose RowSourceType is "Table/Query" and RowSource is a SELECT statement, the following event handler is executed:
Sub cboLastName_AfterUpdate()
Me.RecordsetClone.FindFirst " = '" & Me! & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
cboLastName =...
|
by: fieldling |
last post by:
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
Option Compare Database
Public Function fImportdata()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs =...
|
by: troy_lee |
last post by:
I have an unbound form. I have an Add New procedure that saves a new
record to the database. What is the best way to update an existing
record? In other words, clicking my "Submit record" button creates
another new instance of the record when all I want is to update a
field or two and save over the existing record.
Thanks in advance.
Troy
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |