Update or CancelUpdate without AddNew or Edit

[bhcob1]

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

[MMcCarthy]

I honestly can't tell from your code. When this error occurs what line is highlighted in yellow in the VBA editor?

Mary

[bhcob1]

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]

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

[bhcob1]

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]

Remove the colon at the end of Else. Does this make any difference?

[MMcCarthy]

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

[bhcob1]

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]

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]

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.