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

Update or CancelUpdate without AddNew or Edit

bhcob1
P: 19
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3.  
  4. Private Sub cmdEditRecord_Click()
  5. 'When user clicks this button, Edit capabilities enable
  6.  
  7.     ' Trap for Errors
  8.     On Error Resume Next
  9.  
  10.     Me.CSOC_Number.Locked = False
  11.     Me.Issue.Locked = False
  12.     Me.Title.Locked = False
  13.     Me.Comment.Locked = False
  14.     Me.Workflow_Status.Locked = False
  15.     Me.NHI_Delivery_Reference.Locked = False
  16.     Me.NHI_Delivery_Date.Locked = False
  17.     Me.AA_Delivery_Reference.Locked = False
  18.     Me.AA_Delivery_Date.Locked = False
  19.     Me.CoA_Response_Reference.Locked = False
  20.     Me.CoA_Response_Date.Locked = False
  21.     Me.NQO__Europe__CSOC_Approval.Locked = False
  22.     Me.NQO__Europe__CSOC_Approval_Date.Locked = False
  23.     Me.NQO__Europe_.Locked = False
  24.  
  25.     Me.NQO__Europe__Approval_Reference.Locked = False
  26.     Me.NQO__Australia__Approval_Reference.Locked = False
  27.     Me.NQO__Australia_.Locked = False
  28.  
  29.     Me.NQO__Australia__CSOC_Approval.Locked = False
  30.     Me.NQO__Australia__CSOC_Approval_Date.Locked = False
  31.  
  32.  
  33.     'Focus must be set to another control to avoid error when control is disabled
  34.     [lstCSOC].SetFocus
  35.     Me.cmdEditRecord.Enabled = False
  36.     Me.cmdDeleteRecord.Enabled = True
  37.  
  38.     ' Code to give the detail section a different colour when in view mode and edit mode
  39.     Me.Detail.BackColor = RGB(255, 255, 255)
  40.  
  41. End Sub
  42.  
  43. Private Sub Form_Current()
  44.  
  45.     ' Trap for Errors
  46.     On Error Resume Next
  47.  
  48.     'Sets value selected in related list to Null every time the user changes record
  49.     Me![lstReqAndCSOC2] = Null
  50.  
  51.     'Requery all the lists displaying relationships
  52.     'Updates every time the user changes records
  53.     Me!lstCSOC.Requery
  54.     Me!lstReqAndCSOC2.Requery
  55.  
  56.     'Removes users ability to Add, Edit and Delete
  57.     'Exception is if user is on new form
  58.     Dim fStatus As Boolean
  59.     Dim nfStatus As Boolean
  60.     fStatus = Me.NewRecord
  61.     nfStatus = Not fStatus
  62.  
  63.     Me.CSOC_Number.Locked = nfStatus
  64.     Me.Issue.Locked = nfStatus
  65.     Me.Title.Locked = nfStatus
  66.     Me.Comment.Locked = nfStatus
  67.     Me.Workflow_Status.Locked = nfStatus
  68.     Me.NHI_Delivery_Reference.Locked = nfStatus
  69.     Me.NHI_Delivery_Date.Locked = nfStatus
  70.     Me.AA_Delivery_Reference.Locked = nfStatus
  71.     Me.AA_Delivery_Date.Locked = nfStatus
  72.     Me.CoA_Response_Reference.Locked = nfStatus
  73.     Me.CoA_Response_Date.Locked = nfStatus
  74.     Me.NQO__Europe__CSOC_Approval.Locked = nfStatus
  75.     Me.NQO__Europe__CSOC_Approval_Date.Locked = nfStatus
  76.     Me.NQO__Europe_.Locked = nfStatus
  77.     Me.NQO__Europe__Approval_Reference.Locked = nfStatus
  78.     Me.NQO__Australia__Approval_Reference.Locked = nfStatus
  79.     Me.NQO__Australia_.Locked = nfStatus
  80.     Me.NQO__Australia__CSOC_Approval.Locked = nfStatus
  81.     Me.NQO__Australia__CSOC_Approval_Date.Locked = nfStatus
  82.  
  83.     cmdEditRecord.Enabled = Not fStatus
  84.     cmdDeleteRecord.Enabled = fStatus
  85.  
  86.  
  87. ' If it is on a new Record
  88. ' Many of the functions cannot be performed on a new record, hence the need for the if statement
  89.     If (fStatus = True) Then
  90.         ' Code to give the detail section a different colour when in view mode and edit mode
  91.         Me.Detail.BackColor = RGB(255, 255, 255)
  92.         Else:
  93.         Me.Detail.BackColor = RGB(211, 211, 211)
  94.  
  95.         ' This code automatically finds the NQO's for the Substantiation File from the requirements table
  96.         ' variable used to retrieve data from the Requirements table
  97.         Dim var1, var2 As Variant
  98.         ' Finds the Requirement for the current CSOC on the form
  99.         var1 = DLookup("[Requirement ID]", "tblReqAndCSOC", "Number =Forms![frmCSOC]![CSOC Number]")
  100.         ' Updates the Relevant NQO for CSOC Approval (Europe) on the CSOC form using data from the Requirements table
  101.         var2 = DLookup("[Relevant NQO for CSOC Approval (Australia)]", "tblReq", "[Requirement ID]=" & "'" & var1 & "'")
  102.         Me.NQO__Australia_ = var2
  103.         ' Updates the Relevant NQO for CSOC Approval (Europe) on the CSOC form using data from the Requirements table
  104.         var2 = DLookup("[Relevant NQO for CSOC Approval (Europe)]", "tblReq", "[Requirement ID]=" & "'" & var1 & "'")
  105.         Me.NQO__Europe_ = var2
  106.  
  107.     End If
  108.  
  109. 'Code to display date field to enter if CSOC has been approved
  110.     If ([NQO (Europe) CSOC Approval] = "1 - Approved") Then
  111.                 Me.NQO__Europe__CSOC_Approval_Date.Enabled = True
  112.                 Me.NQO__Europe__Approval_Reference.Enabled = True
  113.         Else:   Me.NQO__Europe__CSOC_Approval_Date.Enabled = False
  114.                 Me.NQO__Europe__Approval_Reference.Enabled = False
  115.     End If
  116.  
  117. 'Code to display date field to enter if CSOC has been approved
  118.     If ([NQO (Australia) CSOC Approval] = "1 - Approved") Then
  119.                 Me.NQO__Australia__CSOC_Approval_Date.Enabled = True
  120.                 Me.NQO__Australia__Approval_Reference.Enabled = True
  121.         Else:   Me.NQO__Australia__CSOC_Approval_Date.Enabled = False
  122.                 Me.NQO__Australia__Approval_Reference.Enabled = False
  123.     End If
  124.  
  125. End Sub
  126.  
  127. Private Sub lstCSOC_AfterUpdate()
  128.  
  129.     ' Trap for Errors
  130.     On Error Resume Next
  131.  
  132.     ' Requrery all the related list after the navigation list has been changed
  133.     Me!lstReqAndCSOC2.Requery
  134.  
  135.     ' Find the record that matches the control.
  136.     Dim rs As Object
  137.     Set rs = Me.Recordset.Clone
  138.     rs.FindFirst "[ID] = " & Str(Nz(Me![lstCSOC], 0))
  139.     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  140.  
  141. End Sub
  142.  
  143.  
  144.  
  145. 'Code to display date field to enter if CSOC has been approved
  146. Private Sub NQO__Australia__CSOC_Approval_Change()
  147.     If ([NQO (Australia) CSOC Approval] = "1 - Approved") Then
  148.                 Me.NQO__Australia__CSOC_Approval_Date.Enabled = True
  149.                 Me.NQO__Australia__Approval_Reference.Enabled = True
  150.         Else:   Me.NQO__Australia__CSOC_Approval_Date.Enabled = False
  151.                 Me.NQO__Australia__Approval_Reference.Enabled = False
  152.                 Me.NQO__Australia__CSOC_Approval_Date = ""
  153.                 Me.NQO__Australia__Approval_Reference = ""
  154.     End If
  155. End Sub
  156.  
  157.  
  158. 'Code to display date field to enter if CSOC has been approved
  159. Private Sub NQO__Europe__CSOC_Approval_Change()
  160.         If ([NQO (Europe) CSOC Approval] = "1 - Approved") Then
  161.                 Me.NQO__Europe__CSOC_Approval_Date.Enabled = True
  162.                 Me.NQO__Europe__Approval_Reference.Enabled = True
  163.         Else:   Me.NQO__Europe__CSOC_Approval_Date.Enabled = False
  164.                 Me.NQO__Europe__Approval_Reference.Enabled = False
  165.                 Me.NQO__Europe__CSOC_Approval_Date = ""
  166.                 Me.NQO__Europe__Approval_Reference = ""
  167.     End If
  168. End Sub
  169.  
  170.  
  171.  
  172. Private Sub CSOC_Number_AfterUpdate()
  173.  
  174. ' Trap for Errors
  175. On Error Resume Next
  176.  
  177. ' Form is refreshed
  178. ' Refreshing of form will only take place if Issue number and CSOC both contain data
  179.     If Not IsNull(Me.Issue) Then
  180.         DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
  181.     End If
  182.  
  183. End Sub
  184.  
  185. Private Sub Issue_AfterUpdate()
  186.  
  187. ' Trap for Errors
  188. On Error Resume Next
  189.  
  190. ' Form is refreshed
  191. ' Refreshing of form will only take place if Issue number and CSOC both contain data
  192. If Not IsNull(Me.CSOC_Number) Then
  193.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
  194. End If
  195.  
  196. 'After user has entered in the Issue, the alpha characters will be capitalised
  197.     Me.Issue = UCase(Me.Issue)
  198.  
  199. End Sub
  200.  
  201. Private Sub Form_Activate()
  202. ' The Activate() Event triggers whenever another window is closed and/or this window takes focus
  203.  
  204. ' Trap for Errors
  205. On Error Resume Next
  206.  
  207. ' Refresh Form, Required so that lists show correct relationships
  208. DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
  209.  
  210. ' If it is on a new Record
  211.     Dim fStatus As Boolean
  212.     fStatus = Me.NewRecord
  213. ' If it is on a new Record
  214. ' Many of the functions cannot be performed on a new record, hence the need for the if statement
  215.     If (fStatus = True) Then
  216.         Else:
  217.  
  218.         ' This code automatically finds the NQO's for the CSOC from the requirements table
  219.         ' To variables used to retrieve data from the Requirements table
  220.         Dim var1, var2 As Variant
  221.  
  222.         ' Finds the Requirement for the current CSOC on the form
  223.         var1 = DLookup("[Requirement ID]", "tblReqAndCSOC", "Number =Forms![frmCSOC]![CSOC Number]")
  224.  
  225.         ' Updates the Relevant NQO for CSOC Approval (Europe) on the CSOC form using data from the Requirements table
  226.         var2 = DLookup("[Relevant NQO for CSOC Approval (Australia)]", "tblReq", "[Requirement ID]=" & "'" & var1 & "'")
  227.         Me.NQO__Australia_ = var2
  228.  
  229.         ' Updates the Relevant NQO for CSOC Approval (Europe) on the CSOC form using data from the Requirements table
  230.         var2 = DLookup("[Relevant NQO for CSOC Approval (Europe)]", "tblReq", "[Requirement ID]=" & "'" & var1 & "'")
  231.         Me.NQO__Europe_ = var2
  232.  
  233.     End If
  234.  
  235.  
End Sub
Feb 20 '07 #1
Share this Question
Share on Google+
9 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
I honestly can't tell from your code. When this error occurs what line is highlighted in yellow in the VBA editor?

Mary
Feb 20 '07 #2

bhcob1
P: 19
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.
Feb 20 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 20 '07 #4

bhcob1
P: 19
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

Expand|Select|Wrap|Line Numbers
  1.  
  2.  If (fStatus = True) Then
  3.         Else:
  4.  
  5.         ' This code automatically finds the NQO's for the Substantiation File from the requirements table
  6.         ' variable used to retrieve data from the Requirements table
  7.         Dim var1, var2 As Variant
  8.         ' Finds the Requirement for the current CSOC on the form
  9.         var1 = DLookup("[Requirement ID]", "tblReqAndCSOC", "Number =Forms![frmCSOC]![CSOC Number]")
  10.         ' Updates the Relevant NQO for CSOC Approval (Europe) on the CSOC form using data from the Requirements table
  11.         var2 = DLookup("[Relevant NQO for CSOC Approval (Australia)]", "tblReq", "[Requirement ID]=" & "'" & var1 & "'")
  12.         Me.NQO__Australia_ = var2
  13.         ' Updates the Relevant NQO for CSOC Approval (Europe) on the CSOC form using data from the Requirements table
  14.         var2 = DLookup("[Relevant NQO for CSOC Approval (Europe)]", "tblReq", "[Requirement ID]=" & "'" & var1 & "'")
  15.         Me.NQO__Europe_ = var2
  16.  
  17.  
Which is in both the Form_Current() and Form_Activate
Feb 21 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Remove the colon at the end of Else. Does this make any difference?
Feb 21 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 21 '07 #7

bhcob1
P: 19
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Form_Current()
  3.  
  4.     ' Trap for Errors
  5.     On Error Resume Next
  6.  
  7.     'Sets value selected in related list to Null every time the user changes record
  8.     Me![lstReqAndCSOC2] = Null
  9.  
  10.     'Requery all the lists displaying relationships
  11.     'Updates every time the user changes records
  12.     Me!lstCSOC.Requery
  13.     Me!lstReqAndCSOC2.Requery
  14.  
  15.     'Removes users ability to Add, Edit and Delete
  16.     'Exception is if user is on new form
  17.     Dim fStatus As Boolean
  18.     Dim nfStatus As Boolean
  19.     fStatus = Me.NewRecord
  20.     nfStatus = Not fStatus
  21.  
  22.     Me.CSOC_Number.Locked = nfStatus
  23.     Me.Issue.Locked = nfStatus
  24.     Me.Title.Locked = nfStatus
  25.     Me.Comment.Locked = nfStatus
  26.     Me.Workflow_Status.Locked = nfStatus
  27.     Me.NHI_Delivery_Reference.Locked = nfStatus
  28.     Me.NHI_Delivery_Date.Locked = nfStatus
  29.     Me.AA_Delivery_Reference.Locked = nfStatus
  30.     Me.AA_Delivery_Date.Locked = nfStatus
  31.     Me.CoA_Response_Reference.Locked = nfStatus
  32.     Me.CoA_Response_Date.Locked = nfStatus
  33.     Me.NQO__Europe__CSOC_Approval.Locked = nfStatus
  34.     Me.NQO__Europe__CSOC_Approval_Date.Locked = nfStatus
  35.     Me.NQO__Europe_.Locked = nfStatus
  36.     Me.NQO__Europe__Approval_Reference.Locked = nfStatus
  37.     Me.NQO__Australia__Approval_Reference.Locked = nfStatus
  38.     Me.NQO__Australia_.Locked = nfStatus
  39.     Me.NQO__Australia__CSOC_Approval.Locked = nfStatus
  40.     Me.NQO__Australia__CSOC_Approval_Date.Locked = nfStatus
  41.  
  42.     cmdEditRecord.Enabled = Not fStatus
  43.     cmdDeleteRecord.Enabled = fStatus
  44.  
  45.  
  46. ' If it is on a new Record
  47. ' Many of the functions cannot be performed on a new record, hence the need for the if statement
  48.     If (fStatus = True) Then
  49.         ' Code to give the detail section a different colour when in view mode and edit mode
  50.  
  51.         Else:
  52.  
  53.         ' This code automatically finds the NQO's for the Substantiation File from the requirements table
  54.         ' variable used to retrieve data from the Requirements table
  55.         Dim var1, var2 As Variant
  56.         Me.NQO__Europe_.Locked = False
  57.         Me.NQO__Australia_.Locked = False
  58.         ' Finds the Requirement for the current CSOC on the form
  59.         var1 = DLookup("[Requirement ID]", "tblReqAndCSOC", "Number =Forms![frmCSOC]![CSOC Number]")
  60.         ' Updates the Relevant NQO for CSOC Approval (Europe) on the CSOC form using data from the Requirements table
  61.         var2 = DLookup("[Relevant NQO for CSOC Approval (Australia)]", "tblReq", "[Requirement ID]=" & "'" & var1 & "'")
  62.         Me.NQO__Australia_ = var2
  63.         ' Updates the Relevant NQO for CSOC Approval (Europe) on the CSOC form using data from the Requirements table
  64.         var2 = DLookup("[Relevant NQO for CSOC Approval (Europe)]", "tblReq", "[Requirement ID]=" & "'" & var1 & "'")
  65.         Me.NQO__Europe_ = var2
  66.         Me.NQO__Europe_.Locked = True
  67.         Me.NQO__Australia_.Locked = True
  68.     End If
  69.  
  70. 'Code to display date field to enter if CSOC has been approved
  71.     If ([NQO (Europe) CSOC Approval] = "1 - Approved") Then
  72.                 Me.NQO__Europe__CSOC_Approval_Date.Enabled = True
  73.                 Me.NQO__Europe__Approval_Reference.Enabled = True
  74.         Else:   Me.NQO__Europe__CSOC_Approval_Date.Enabled = False
  75.                 Me.NQO__Europe__Approval_Reference.Enabled = False
  76.     End If
  77.  
  78. 'Code to display date field to enter if CSOC has been approved
  79.     If ([NQO (Australia) CSOC Approval] = "1 - Approved") Then
  80.                 Me.NQO__Australia__CSOC_Approval_Date.Enabled = True
  81.                 Me.NQO__Australia__Approval_Reference.Enabled = True
  82.         Else:   Me.NQO__Australia__CSOC_Approval_Date.Enabled = False
  83.                 Me.NQO__Australia__Approval_Reference.Enabled = False
  84.     End If
  85.  
  86. End Sub
  87.  
  88.  
Feb 21 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
This is probably the correct solution. Locking controls on a form does prevent certain actions from being preformed.

Hope it continues to work.

Mary
Feb 21 '07 #9

NeoPa
Expert Mod 15k+
P: 31,307
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.
Feb 22 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.