Results 1 to 3 of 3
  1. #1
    brharrii's Avatar
    brharrii is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2012
    Location
    Seattle, WA
    Posts
    63

    Smile Audit Trail - Pull User Name From Table, Office 2010

    I am new to working with VBA, so it is possible that I am going about this a harder way than is necessary.



    Scenario: I am working in a product specification database and I've setup an audit trail to record any changes made through a form to my table. I've also setup a user table with a form that requires users to login to the database using a user name and password.

    Objective: The code has a few bugs I'm working out one at a time. The big one I'm working on right now is changing the source of the audit trail code that records "username" to refer to the user name used to login to access. Currently it is using the user name used to login to windows, which isn't ideal because not all users have unique windows logins (not something I can change unfortunately).

    This is the code I believe I need to change:

    Code:
    & cDQ & Environ("username") & cDQ & ", " _
    I havent been able to find a good way to change it to refer to the username used to login to access though. If anyone has an answer or any resources that I could refer to to learn more about this process it would be greatly appreciated. Thank you!

    Also here is the code in its entirety incase that is helpful, Thanks again!

    Code:
    Const cDQ As String = """"
    Sub AuditTrail(frm As Form, recordid As Long)   '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.
    Select Case ctl.ControlType
    Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox, acOptionButton, acToggleButton
    If .Value <> .OldValue Then
    varBefore = .OldValue
    varAfter = .Value
    strControlName = .Name         'Build INSERT INTO statement.
    strSQL = "INSERT INTO " _
    & "tblUpdateAudit (EditDate, User, RecordID, SourceTable, " _
    & " SourceField, BeforeValue, AfterValue) " _
    & "VALUES (Now()," _
    & cDQ & Environ("username") & cDQ & ", " _
    & recordid & ", " _
    & cDQ & frm.RecordSource & cDQ & ", " _
    & cDQ & .Name & cDQ & ", " _
    & cDQ & varBefore & cDQ & ", " _
    & cDQ & varAfter & cDQ & ")"          'View evaluated statement in Immediate window.
    
    Debug.Print strSQL
    CurrentDb.Execute strSQL, dbFailOnError
    End If
    End Select
    End With
    Next
    Set ctl = Nothing
    Exit Sub
    ErrHandler:   MsgBox Err.Description & vbNewLine _
    & Err.Number, vbOKOnly, "Error"
    End Sub

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    How are duplicate usernames possible - these users are not on a common network?

    Options:

    1. Set a textbox on a form that is always open (like a main menu) to the Access login username.

    2. Set a global declared variable
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    brharrii's Avatar
    brharrii is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2012
    Location
    Seattle, WA
    Posts
    63
    Quote Originally Posted by June7 View Post
    How are duplicate usernames possible - these users are not on a common network?

    Options:

    1. Set a textbox on a form that is always open (like a main menu) to the Access login username.

    2. Set a global declared variable

    Not that there are multiple duplicate usernames, rather that there is one windows username used by multiple people. That is why I added the access login credentials. Thanks for your response, I'll play with this idea

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

Similar Threads

  1. Audit Trail
    By bigroo in forum Programming
    Replies: 4
    Last Post: 02-22-2012, 07:55 PM
  2. audit trail
    By slimjen in forum Forms
    Replies: 1
    Last Post: 10-13-2011, 01:52 PM
  3. Audit Trail for SubForms Not Working
    By PinkNinja in forum Access
    Replies: 6
    Last Post: 01-07-2011, 11:03 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

Tags for this Thread

Posting Permissions

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