Solved Error 2467 (1 Viewer)

Falcon88

Registered User.
Local time
Today, 15:04
Joined
Nov 4, 2014
Messages
216
Hi all

When i am using this code for audit , i want to get the text on the label related to the controls ( not control name ) , it works good on the single forms , but gives error 2467 when using it on continuous forms .
Code:
Option Compare Database
Option Explicit

Public Function Audit_Trail(MyForm As Form, UniqID_Field As String, UniqID As String, Optional bCause As Boolean)
On Error GoTo Err_Audit_Trail
    
'ACC2000: How to Create an Audit Trail of Record Changes in a Form
'http://support.microsoft.com/default.aspx?scid=kb;en-us;197592
    
    'Dim MyForm As Form
    Dim ctl As Control
    Dim ccnt As Control
    Dim sUser As String
    
    Dim strSQL As String
    Const cQUOTE = """" 'Thats 2 quotes in sequence
        
    Dim Action, nullval As String
    nullval = "Null"
    
    sUser = Environ("UserName") 'get the users login name
    
    'If new record, record it in audit trail and exit function.
    If MyForm.NewRecord = True Then
        Action = "*** New Record ***"
        'Broken down into 4 separate variables for ease of view and troubleshooting
        strSQL = "INSERT INTO tblAudit ( [User], [DateTime], UniqID_Field, UniqID, Form, [Action])"
        strSQL = strSQL & " SELECT " & cQUOTE & sUser & cQUOTE & ", " & cQUOTE & Now & cQUOTE & " , "
        strSQL = strSQL & cQUOTE & UniqID_Field & cQUOTE & ", " & cQUOTE & UniqID & cQUOTE & ", "
        strSQL = strSQL & cQUOTE & MyForm.Name & cQUOTE & ", " & cQUOTE & Action & cQUOTE & ";"
        
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        
        Exit Function
    End If
    
    Dim changecnt As Integer
    changecnt = 0
    
    'Check each data entry control for change and record old value of the control.
    For Each ccnt In MyForm.Controls
    
    Select Case ccnt.ControlType
      Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
        If ccnt.Name Like "*" & "txt" & "*" Then GoTo TryNextCCNT   'Skip AuditTrail field.
        If (ccnt.Value <> ccnt.OldValue) Or _
           (IsNull(ccnt.Value) And Len(ccnt.OldValue) > 0 Or ccnt.Value = "" And Len(ccnt.OldValue) > 0) Then
          changecnt = changecnt + 1
        End If
    End Select
    
TryNextCCNT:
  Next ccnt
        
    If bCause = True Then
            If changecnt > 0 Then
              gstrReason = InputBox("Reason for change(s)?", "Reason for change(s)?")
            End If
     End If
        
    'Check each data entry control for change and record old value of the control.
    For Each ctl In MyForm.Controls
        
    'Only check data entry type controls.
    Select Case ctl.ControlType
    Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
        If ctl.Name Like "*" & "txt" & "*" Then GoTo TryNextControl 'Skip AuditTrail field.
        If ctl.Value <> ctl.OldValue Then
            Action = "*** Updated Record ***"
        
            'Broken down into 4 separate variables for ease of view and troubleshooting
            strSQL = "INSERT INTO tblAudit ( [User], [DateTime], UniqID_Field, UniqID, Form, Field, Prev_Value, New_Value, [Action], Reason)"
            strSQL = strSQL & " SELECT " & cQUOTE & sUser & cQUOTE & ", " & cQUOTE & Now & cQUOTE & " , "
            strSQL = strSQL & cQUOTE & UniqID_Field & cQUOTE & ", " & cQUOTE & UniqID & cQUOTE & ", "
            strSQL = strSQL & cQUOTE & MyForm.Name & cQUOTE & ", " & cQUOTE & ctl.Controls(0).Caption & cQUOTE & ", " & cQUOTE & ctl.OldValue & cQUOTE
            strSQL = strSQL & ", " & cQUOTE & ctl.Value & cQUOTE & ", " & cQUOTE & Action & cQUOTE & ", " & cQUOTE & gstrReason & cQUOTE & ";"
            ' ' i replace ctl.Name with ctl.Controls(0).Caption , this works good for single forms only
            DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
            
        'If old value is Null and new value is not Null
        ElseIf IsNull(ctl.OldValue) And Len(ctl.Value) > 0 Or ctl.OldValue = "" And Len(ctl.Value) > 0 Then
            Action = "*** Added Info to Record ***"
        
            'Broken down into 4 separate variables for ease of view and troubleshooting
             strSQL = "INSERT INTO tblAudit ( [User], [DateTime], UniqID_Field, UniqID, Form, Field, Prev_Value, New_Value, [Action])"
             strSQL = strSQL & " SELECT " & cQUOTE & sUser & cQUOTE & ", " & cQUOTE & Now & cQUOTE & " , "
             strSQL = strSQL & cQUOTE & UniqID_Field & cQUOTE & ", " & cQUOTE & UniqID & cQUOTE & ", "
             strSQL = strSQL & cQUOTE & MyForm.Name & cQUOTE & ", " & cQUOTE & ctl.Controls(0).Caption & cQUOTE & ", " & cQUOTE & nullval & cQUOTE
             strSQL = strSQL & ", " & cQUOTE & ctl.Value & cQUOTE & ", " & cQUOTE & Action & cQUOTE & ";"
             ' ' i replace ctl.Name with ctl.Controls(0).Caption , this works good for single forms only , this works good for single forms only
             DoCmd.SetWarnings False
             DoCmd.RunSQL strSQL
             DoCmd.SetWarnings True
            
        'If new value is Null and old value is not Null
        ElseIf IsNull(ctl.Value) And Len(ctl.OldValue) > 0 Or ctl.Value = "" And Len(ctl.OldValue) > 0 Then
             Action = "*** Removed Info to Record ***"

            'Broken down into 4 separate variables for ease of view and troubleshooting
             strSQL = "INSERT INTO tblAudit ( [User], [DateTime], UniqID_Field, UniqID, Form, Field, Prev_Value, New_Value, [Action], Reason)"
             strSQL = strSQL & " SELECT " & cQUOTE & sUser & cQUOTE & ", " & cQUOTE & Now & cQUOTE & " , "
             strSQL = strSQL & cQUOTE & UniqID_Field & cQUOTE & ", " & cQUOTE & UniqID & cQUOTE & ", "
             strSQL = strSQL & cQUOTE & MyForm.Name & cQUOTE & ", " & cQUOTE & ctl.Controls(0).Caption & cQUOTE & ", " & cQUOTE & ctl.OldValue & cQUOTE
             strSQL = strSQL & ", " & cQUOTE & nullval & cQUOTE & ", " & cQUOTE & Action & cQUOTE & ", " & cQUOTE & gstrReason & cQUOTE & ";"
             ' ' i replace ctl.Name with ctl.Controls(0).Caption , this works good for single forms only
             DoCmd.SetWarnings False
             DoCmd.RunSQL strSQL
             DoCmd.SetWarnings True
         End If

    End Select
    
TryNextControl:
    Next ctl
    
Exit_Audit_Trail:
    Exit Function
    
Err_Audit_Trail:
    If Err.Number = 2001 Then 'You canceled the previous operation.
      'do nothing
    Else
        Beep
        MsgBox Err.Number & " - " & Err.Description
    End If
    Resume Exit_Audit_Trail
    
End Function

Please how can you help me to fix this problem ?
 

isladogs

CID VIP
Local time
Today, 12:04
Joined
Jan 14, 2017
Messages
14,038
Please help readers to help you.
On which line does the error occur? What is the error description for error 2467
 

Falcon88

Registered User.
Local time
Today, 15:04
Joined
Nov 4, 2014
Messages
216
Please help readers to help you.
On which line does the error occur? What is the error description for error 2467


On the form_beforeupdate event , when calling the function :
Code:
Sub form_beforeupdate(cancel as integer)

Call Audit_Trail(me, "OrderID", OrderID.value, True)

End sub
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 06:04
Joined
Feb 28, 2001
Messages
18,393
Error 2467 is "Object is closed or does not exist."

You are doing this in a Form_BeforeUpdate context so we know that a form must be open.

In a form context, Me is always valid, pointing to the open form object. "OrderID" (as a quoted string) is always valid because literal string constants are always valid. True is also a known constant and thus should be valid as well.

That leaves OrderID.Value as your culprit. Unfortunately, that bunch of code you posted as the AuditTrail code will have nothing to do with the actual error. If the call had worked, you might have gotten into that code.

The question has to be this: In the context of your form on which that event is triggered, what is OrderID and how is it defined?
 

Falcon88

Registered User.
Local time
Today, 15:04
Joined
Nov 4, 2014
Messages
216
OrderID is a bound control on that subform , it is a long datatype .
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 06:04
Joined
Feb 28, 2001
Messages
18,393
To which form is that Form_BeforeUpdate event attached? The main form or the sub-form? If it is an event for the main form and the control is on the sub-form then OrderID is not visible because it is not "in scope." However, I will not jump to conclusions. I'll just wait for the answer to the question.
 

Falcon88

Registered User.
Local time
Today, 15:04
Joined
Nov 4, 2014
Messages
216
If i use single form there no problem .
The problem only in continuous form ( if it was main or sub form )
 

Falcon88

Registered User.
Local time
Today, 15:04
Joined
Nov 4, 2014
Messages
216
See to the attachment
 

Attachments

  • MyAuditTrail.zip
    44 KB · Views: 17

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 06:04
Joined
Feb 28, 2001
Messages
18,393
Sorry, I looked at it a bit but can't see anything obvious. I reiterate that it SEEMS like the ONLY value that can't be instantly validated is the OrderID.Value. Can you put a breakpoint there (on the Call statement) and examine the values?
 

Falcon88

Registered User.
Local time
Today, 15:04
Joined
Nov 4, 2014
Messages
216
Sorry, I looked at it a bit but can't see anything obvious. I reiterate that it SEEMS like the ONLY value that can't be instantly validated is the OrderID.Value. Can you put a breakpoint there (on the Call statement) and examine the values?

Please try to change data in any field or enter new records in the continuous form ( frmEmploeeList or in subform ) , then go to a new record and you will notice the error message that will appear.
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 06:04
Joined
Feb 28, 2001
Messages
18,393
Unfortunately, I have a hard policy to never execute someone else's database no matter how benign it might appear. My question stands: Can you put a breakpoint on the CALL that is giving you trouble? And if you do, then open the Immediate window and type DEBUG.PRINT ORDERID.VALUE to see what it tells you in that context.
 

arnelgp

error reading drive A:
Local time
Today, 20:04
Joined
May 7, 2009
Messages
10,888
i downloaded your db.
i renamed some of your control to English (from arabic).
when the Names are in arabic, i am encountering error.

i did not use the Label of the textboxes but instead
i added Tag to each textbox (on design view, Property Sheet->Format->Tag).
i used these Tags in your Audit Trail code.
 

Attachments

  • Repaired_MyAuditTrail.zip
    80.4 KB · Views: 14

Users who are viewing this thread

Top Bottom