Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2007

    Unanswered: How To Modify The Following Audit Trail

    Sample database Credit due to Ghoudson.

    tblEmployees Holds Passwords
    tEmployees holds employees

    User Name David Password (david) case sensitive.

    This sample Database will track any changes made by user however I cant get the audit field to hook up with my Current login form to track user changes. Also i added a couple of text boxes to the tEmployees form One Called User The other Modified On. How Can I Put the User Name In The Text Box Called User and The date in Modified On when the Employee makes Changes.

    There Are 2 Modules In the Database

    Audit Trail Will Not Be Visiable However in this case I Made It Visable.
    User Visable
    Modified On Visable.

    Any Ideas

    Thanks In Advance.
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2004
    Madison, WI
    I'm guessing that you have multiple passwords for 1 employee and that's why you're seperating the Password table verses just having a Password and authority field in the tEmployees table.

    This routine is a way to get the user Login name:

    Audit trail tracking can be done at a simple level (ie. any field changes on the record but you don't care which field it is) by having 4 fields in the main data table:
    DateEntered (date field)
    EnteredBy (text field)
    DateModified (date field)
    ModifiedBy (text field)

    Have all 4 of these fields on the form (locked = true and visible = false or true) and set them up accordingly:

    DateEntered is dafaulted to =Date(). EnteredBy defaults to =Getuser() - using the getuser() function in the modules in the download of the sample link provided.

    Then in the BeforeUpdate() event of the "Form", set DateModified = Date() and ModifiedBy = Getuser() via vba code in that event (getuser() can be used anywhere, even in queries as an expression to append to a field.)

    If you want to get at a more detailed level than that, you'll need to have some kind of table to track which field of data was changed.

    I hope this helps.
    Last edited by pkstormy; 08-12-07 at 14:37.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Aug 2007
    Thanks I'll Try That for those fields however the The Audit Trail text box on the tEmployees form already records the new record and records what field
    has been changed if any. Try Changing any of the fields in the sample databse and you will see it has been recorded what field has been changed.

    The Problem I'm Having with the Audit Trail text box On temployees is that it Says New Record recorded By Admin Not the Current user (David) which is what I Want to happen.

  4. #4
    Join Date
    Dec 2004
    Madison, WI
    I'm gathering you are looking to set the default of a new record to the user login?

    If so, import the modules in the example and set the default value of the "User" field to =getuser().

    It doesn't matter where the getuser() is used, it will always return the windows login.

    Or if you want it to put the "User" in when changes occur, in the BeforeUpdate Event or in your module for that form, put...
    = getuser()

    The getuser() will return whoever is logged into the computer. I would recommend storing this loginID in the tEmployees to compare security levels against.

    If in you tEmployees table you also store the user LoginID, and for example, you needed to return the user's first name (ie. David) this would also be easy...

    To return a user's first name....
    In the example, there is a routine which returns true or false if the user is in the dbo_Admin table. Use this routine and modify it a little to return the user's first name or authority level or whatever in the your tEmployees table.
    Function retFName() As string
    Dim LID As Variant
    LID = GetUser()
    Dim strSQL As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    strSQL = "Select * from tEmployees where LoginID = '" & LID & "'"
    rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    If rs.EOF And rs.BOF Then
    retFName = "Not Found"
    retFName = rs!FName
    End If
    Set rs = Nothing
    End Function

    and just set this to the User field when you need me!User = retFName(getuser()) or as a default value =retFName(getuser())
    Last edited by pkstormy; 08-12-07 at 15:28.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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