Full Version: Error 2465
UtterAccess Forums > Microsoft® Access > Access Forms
jmcwk
Have copied the code for the Audit Trail into my DB frm KNK Consulting written by Richard Rensel and am getting a 2495 Error "Can't find the field Updates referred to in your expression" The Field (control IS on the form) although it is in the subform rather than the Mainform do not know if that has any bearing on it or not anyway errors out on this line:
CODE
  'Set date and current user if form has been updated.
    MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & "Changes made on " & Now & " by " & strUser & ";"

Thank You
tinygiant
John,
If it's on the subform, you'll have to refer to it differently, unless you the code you're using is behind the subform's source. If the code's behind the main form, refer to the subform control like this:
Forms!frmMyMainForm!ctlMySubformControlName.Form!ctlMyControlName
jmcwk
Thanks Ed, the code is behind the sub in the Before Update event. Below is the entire code.
CODE
Private Sub Form_BeforeUpdate(Cancel As Integer)
'---------------------------------------------------------------------------------
' This code is used to create and audit trail of who made changes to
' the record. The code capture the new value and old value of each
' change along with the date, time and user ID number and enters it
' into the UpDates field in the table. Original code found on
' Microsoft Knowledge Base.
' Added by: Richard Rensel
' Date Added: 27 Mar 2000
' Date Modified: 27 Mar 2000
'----------------------------------------------------------------------------------
On Err GoTo TryNextC
    Dim MyForm As Form
    Dim ctl As Control
    Dim strUser As String
    Set MyForm = Screen.ActiveForm
    strUser = fOSUserName
' Set date and current user if form has been updated.
    MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & "Changes made on " & Now & " by " & strUser & ";"
' If new record, record it in audit trail and exit sub.
    If MyForm.NewRecord = True Then
       MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & "New Record """
       Exit Sub
    End If
' Check each data entry control for change and record old value of 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 = "Updates" Then GoTo TryNextC ' Skip Updates field.
            
             If ctl.Value <> ctl.OldValue Then
                MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & "     " & ctl.Name & ": Changed from: " & ctl.OldValue & " to: " & ctl.Value
             End If
       End Select
TryNextC:
         Next ctl
End Sub
tinygiant
John,
believe your problem is still in the reference to the ActiveForm. When you use Screen.ActiveForm, it returns a reference to the main form, not the subform. Try using Me instead and see how it works out for you.
ChrisO
I’m not sure why you would want to create an entry in your audit trail in the BeforeUpdate event.

By way of explanation and from the comments in the code as posted…
' This code is used to create and audit trail of who made changes to the record.
The BeforeUpdate event has a Cancel argument.
If the user were to Cancel the update would you want that included in the audit trail?
That is the very reason for using the BeforeUpdate event…so that the Update might be cancelled.

More comments from the posted code…
' Set date and current user if form has been updated.
Again, would the form be updated if Cancel were invoked?

Yet more comments from the code…
' If new record, record it in audit trail and exit sub.
Why? The advent of a new record does not necessarily mean that the new record will be created or saved.
So why update the audit trail if all that has happened is that the user inadvertently clicked the ‘New Button’ and subsequently backed out?
It might be to save an audit trail of user stupidly, but that in itself could be stupid.

There are other things wrong with that code…
Set MyForm = Screen.ActiveForm. Question; what form reference is returned by that statement if the code is running in a Sub Form?
fOSUserName is not defined.
Why use Exit Sub?
Why use GoTo TryNextC?
Where does MyForm get set to Nothing?

The problem seems to me…
You are copying/pasting code that might (although I doubt it) have been written in good faith.
Considering the complexity of code (any code) you can not expect that to happen without error.

You must become capable of debugging any code before you use it, else find a workaround.

You can not expect others to debug the code you may get from anywhere, not even this site… not from anywhere.

HAs always, in my opinion…
Your aim should be to ask general questions about the de-bugging of code.
General questions like; how do I go about finding this particular bug?
Then state the particular bug and the circumstances under which it arises.

Even more personal opinion…
By the time you can state the ‘how and when’ of a bug clearly, you probably won’t need to ask the question.

Regards,
Chris.

ETA.
Formatting a wide post.


Edited by: ChrisO on Mon May 8 2:11:21 EDT 2006.
Edited by: ChrisO on Mon May 8 2:14:46 EDT 2006.
tinygiant
Chris, I have a coupla questions for ya:
I've never really used an audit trail before, but I have used the .OldValue property a coupla times.
1) Doesn't the audit trial have to be done in the BeforeUpdate event? Once you update the record, you no longer have access to the .OldValue property because the old value becomes the same as the new value. Where would the old value come from?
2) How is it that the user can Cancel the update? I always have a Cancel (Undo) command button on my forms, but the users can only use it up until they want to save the record, so if the BeforeUpdate event fires for the Form (i.e. when the entire update record is to be updated), it's relatively likely that the record will be saved and, in this case, an audit trail required. I think if I were to use an audit trail, the only thing I would add here would be some error checking/data validation (so I can set the Cancel argument) and then perform the trail save if Cancel was False.
cew657
I'm going to put my 2 cents worth in. I am attaching a sample Audit Trail database I have pieced together from different sources here at UA. It writes all changes to a field to a table called tblAudit. The code appears in the BeforeUpdate event of the frmTesting.
If you have a subform, you can copy this code to the BeforeUpdate event of the subform and change this line Set frm=Forms![frmTesting] to reference your subform which would look like Set frm = Forms![name of main form]![name of subform]
The only thing I don't like about the audit trail you are trying to get to work, is the changes are written to a memo field. How are you going to query this memo field to find changes that happened on a particular date?
Chris' arguments are valid about the user cancelling the changes but the audit trail main intent is to capture the majority of the changes that occur to the database.
HTH
Chad
jmcwk
Chris,Ed,Chad
Thank You for your comments appreciate it
ChrisO
G’day Tiny and you are correct, it was my mistake to use the word Cancel and not Undo.
That method of verifying changes must be done from the Form BeforeUpdate event but not in the Form BeforeUpdate.
So to try and make amends for my mistake I re-wrote the attachment posted by Chad.
There have been a few changes…
The code has been moved to a standard module so that any Form can call it.
The calling of the procedure is done conditionally depending on the user requesting to save.
Reliance on Screen.ActiveForm is not used, for which should now be well-understood reasons.
Works for Forms and Sub-Forms.
Deleting information (conversion to Null) for both before and after has been added.
Reliance on GoTo’s has been removed, except for error handling.
Optional message can now be passed to track user progress, not just changes they make.
Generic global error handling has been included.
Reference dependencies have been removed. (Easier to convert to other Access versions.)
Small A97 demo attached, but here is the bulk of the code as well: -
Behind the Forms…
CODE
Option Explicit
Option Compare Text


Private Sub Form_BeforeUpdate(ByRef intCancel As Integer)
    
    On Error GoTo ErrorHandler
    
    If MsgBox("Save record in Main Form?", vbYesNo) = vbYes Then
        WriteToAuditLog Me
    Else
        Me.Undo
    End If
    
ExitProcedure:
    Exit Sub
    
ErrorHandler:
    DisplayError "Form_BeforeUpdate", Me.Name
    Me.Undo
    Resume ExitProcedure

End Sub


Private Sub Form_Close()

    On Error GoTo ErrorHandler

    WriteToAuditLog Me, "Form_Close"

ExitProcedure:
    Exit Sub
    
ErrorHandler:
    DisplayError "Form_Close", Me.Name
    Resume ExitProcedure

End Sub


Private Sub Form_Open(ByRef intCancel As Integer)

    On Error GoTo ErrorHandler

    WriteToAuditLog Me, "Form_Open"
    
ExitProcedure:
    Exit Sub
    
ErrorHandler:
    DisplayError "Form_Open", Me.Name
    Resume ExitProcedure
    
End Sub

In a standard module…
CODE
Option Explicit
Option Compare Text

Private Const conModuleName As String = "mdlAuditLog"
[color="green"]'
'*******************************************************************************[/color]


Public Sub WriteToAuditLog(ByRef frmThisForm As Form, _
                        Optional vntMessage As Variant)
                        
    Dim ctlC As Control

    On Error GoTo ErrorHandler

    With CurrentDb.OpenRecordset("tblAudit")
        [color="green"]'   If not passed a message, check the controls.[/color]
        If IsMissing(vntMessage) Then
            For Each ctlC In frmThisForm
                Select Case ctlC.ControlType
                    Case acTextBox, acCheckBox, acComboBox
                        If Nz(ctlC.Value, "") <> Nz(ctlC.OldValue, "") Then
                            .AddNew
                                .Fields("FormName") = frmThisForm.Name
                                .Fields("FieldChanged") = ctlC.Name
                                .Fields("FieldChangedFrom") = ctlC.OldValue
                                .Fields("FieldChangedTo") = ctlC.Value
                                .Fields("User") = GetUserName()
                            .Update
                        End If
                End Select
            Next ctlC
        Else
            [color="green"]'   Add the message.[/color]
            .AddNew
                .Fields("FormName") = frmThisForm.Name
                .Fields("Message") = vntMessage
                .Fields("User") = GetUserName()
            .Update
        End If
        .Close
    End With
    
ExitProcedure:
    Exit Sub
    
ErrorHandler:
    DisplayError "WriteToAuditLog", conModuleName
    Resume ExitProcedure

End Sub

That’s just a start and so back to my main point.
Nobody should simply copy/paste code from anywhere (Not that you did but you know what I mean.)
Anybody that does had better be prepared to de-bug the code under their circumstances.
Nobody can expect others to de-bug the code for them.
So that’s about it.
Thanks for bringing that to my attention and sorry about the error on my part.
Regards,
Chris.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.