Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    4

    Question Unanswered: automatic update trail

    Gurus....Is there a way to keep a track of what ever has been updated in a form.??????

    All i want to know is what, when and who updated the records.....

    mofaisy

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    History Log

    Once again...The Access Web can help...

    http://www.mvps.org/access/modules/mdl0021.htm
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Dec 2003
    Posts
    4

    Re: History Log

    but this expample only shows what has been change, i want to know what was the value before and it has been changed to what???
    have a look at the attached file...
    This (AuditTrail2000.mdb) one i have downloaded from the web... but its not working with my code. ( my code is 11K.mdb )


    Originally posted by garethdart
    Once again...The Access Web can help...

    http://www.mvps.org/access/modules/mdl0021.htm
    Attached Files Attached Files
    Last edited by mofaisy; 12-20-03 at 05:22.

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    No problem

    Hang fire...
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    Dec 2003
    Posts
    4

    Re: No problem

    This (AuditTrail2000.mdb) one i have downloaded from the web... but its not working with my code. ( my code is 11K.mdb )

    i want exactly like this (AuditTrail2000.mdb) !!!!!! for some reason (probably my ignorance) its not working for me...




    Originally posted by garethdart
    Hang fire...
    Attached Files Attached Files

  6. #6
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Try this?

    Create a table say tblHistoryLog with fields somehting like

    DateTimeChanged
    UserChanged
    MachineName
    FieldChanged
    OldValue
    NewValue
    RecordID
    CurrentUser

    ######################################

    Code behind form would be something like this;

    Option Compare Database
    Option Explicit
    Public OriginalFieldValue As Variant

    Private Sub txtUnboundTextBox_AfterUpdate()

    If OriginalFieldValue <> Nz(Me.txtUnboundTextBox, "Null!>") Then

    Call AppendHistoryLog(OriginalFieldValue, Me.txtUnboundTextBox, "txtUnboundTextBox", me.RecordID)

    End If

    End Sub

    Private Sub txtUnboundTextBox_BeforeUpdate(Cancel As Integer)

    OriginalFieldValue = Nz(Me.txtUnboundTextBox, "<Null!>")

    End Sub

    (For each field you want to log (or all of them)
    ######################################
    Module something like this

    Public Function AppendHistoryLog(OriginalValue As Variant, NewValue As Variant, FieldChanged As Variant, RecordID As Variant)

    Dim rst As ADODB.Recordset

    Set rst = New ADODB.Recordset

    With rst
    .ActiveConnection = CurrentProject.Connection
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open "select * from tblhistorylog"
    .AddNew
    !DateTimeChanged = Now()
    !UserChanged = Nz(fOSUserName, "<null>")
    !MachineName = Nz(fOSMachineName, "<null>")
    !FieldChanged = Nz(FieldName, "<null>")
    !OldValue = Nz(OldValue, "<null>")
    !NewValue = Nz(NewValue, "<null>")
    !RecordID = Nz(RecordID, "<null>")
    !CurrentUser = Nz(CurrentUser, "<null>")
    .Update
    End With

    rst.Close
    Set rst = Nothing

    End Function

    Also add the FosUsername and FosMachineName from "The Access Web"

    ##############################################

    Full credit here should go to Dev Ashish** The access web>

    http://www.mvps.org/access/

    **I cannot distribute Devs' modules, although the header says you can use them in your own application.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •