Results 1 to 2 of 2
  1. #1
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727

    audit trail

    All using access 2003. I adopted code for an audit trail and I can't get it to work. Below is the code:



    Code:
    Const cDQ As String = """"
    Sub AuditTrail(frm As Form, recordid As Control)  'Track changes to data.
    'recordid identifies the pk field's corresponding
    'control in frm, in order to id record
    Dim ctl As Control
    Dim varBefore As Variant
    Dim varAfter As Variant
    Dim strControlName As String
    Dim strSQL As String
    On Error GoTo ErrHandler
    'Get changed values.
    For Each ctl In frm.Controls
    With ctl
    'Avoid labels and other controls with Value property.
    If .ControlType = acTextBox Then
    If .Value <> .OldValue Then
    varBefore = .OldValue
    varAfter = .Value
    strControlName = .Name  'Build INSERT INTO statement.
    strSQL = "INSERT INTO " _
    & "Audit (EditDate, User, RecordID, SourceTable, " _
    & " SourceField, BeforeValue, AfterValue) " _
    & "VALUES (Now()," _
    & cDQ & Environ("username") & cDQ & ", " _
    & cDQ & recordid.Value & cDQ & ", " _
    & cDQ & frm.RecordSource & cDQ & ", " _
    & cDQ & .Name & cDQ & ", " _
    & cDQ & varBefore & cDQ & ", " _
    & cDQ & varAfter & cDQ & ")"        'View evaluated statement in Immediate window.
    Debug.Print strSQL
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    End If
    End If
    End With
    Next
    Set ctl = Nothing
    Exit Sub
    ErrHandler:
    MsgBox Err.Description & vbNewLine _
    & Err.Number, vbOKOnly, "Error"
    End Sub
    I created the table called audit accordingly and calling it from my form:
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Call AuditTrail(Me, NUM_TRACKINGID)
    End Sub
    It says to use the pk of the form. When I do; it gives me an type mismatch debug error. I thought it was because the table field "recordid" in the audit table is text not number. But when I change this field to number I get a error " this operation is not allowed."
    Can anyone help me with this code please?
    Thanks

  2. #2
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    solved in another forum

Please reply to this thread with any new information or opinions.

Similar Threads

  1. audit Log
    By mwabbe in forum Access
    Replies: 42
    Last Post: 05-30-2011, 07:23 AM
  2. Audit Trail for SubForms Not Working
    By PinkNinja in forum Access
    Replies: 6
    Last Post: 01-07-2011, 11:03 PM
  3. Audit trail not working on form with subform
    By jpkeller55 in forum Access
    Replies: 52
    Last Post: 01-07-2011, 12:39 PM
  4. Audit trail question #2
    By jpkeller55 in forum Access
    Replies: 9
    Last Post: 09-13-2010, 05:55 PM
  5. Audit trail question
    By jpkeller55 in forum Access
    Replies: 5
    Last Post: 09-13-2010, 04:12 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums