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 7721 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,557
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...
|
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...
|
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....
|
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...
|
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:...
| |
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...
|
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...
|
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...
|
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...
|
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,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
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,...
|
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...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |
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...
| |