Audit Trail Module/Code

[tsteele80]

I'm new to modules and VBA. I added code for an audit trail that I found another forum. It works, however I'm having issues with it working properly. The old and new values for the text fields are returning the same data in both fields. My Employee Name field is not returning a value but the EmplID is. I would appriciate any help!!
Added Additional Table with the below fields
FormName (Text)
EmplID (Number)
EmployeeName (Text)
ControlName (Text)
DateChanged (Date/Time)
TimeChanged (Date/Time)
PriorInfo (Text)
NewInfo (Text)
CurrentUser (Text)


Added code to module

Option Compare Database
Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
 
Function fOSUserName() As String
' Returns the login name for Adminstrator use
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function
 
Function TrackChanges()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim strCtl As String
strCtl = Screen.ActiveControl.Name
strSQL = "SELECT Audit.* FROM Audit;"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then rs.MoveLast
With rs
.AddNew
rs!FormName = Screen.ActiveForm.Name
rs!ControlName = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!PriorInfo = Screen.ActiveControl.OldValue
rs!NewInfo = Screen.ActiveControl.Value
rs!CurrentUser = fOSUserName
rs!EmplID = Screen.ActiveForm.EmplID
rs!EmployeeName = Screen.ActiveForm.EmployeeName
.Update
End With
 
Set db = Nothing
Set rs = Nothing

 

[EternalMyrtle]

Let me just preface my response by saying that I am no expert on this at all but I do use the fOSUserName module for tracking field updates.

My only question is: are you entering these into one module or two separate modules?

I think they should be entered separately so that they can be called in whatever code you are using but I am a newbie, too. My fOSUserName module is entered into its own module.

I also noticed that you do not have an EndFunction in your TrackChanges function but maybe you just didn't copy it?

Can you post a link to the forum in which you got the code? I would like to use it, too. Thanks!

 

[EternalMyrtle]

I think I found a link to the code you were using:

http://forums.devarticles.com/micro...dit-trail-of-all-edits-to-database-22382.html

I got it working. I already had the fOSUserName function so I only had to copy TrackChanges, which I copied it into a separate module. Because I wanted it in a global module, I had to replace all the instances of "Me." After doing that, it works perfectly.