Security - Username

[Ted]

Hi All,

We're trying to create an audit trail using a table. We secured the
database. The object
of the audit table is to track which user makes a change to each field. The
problem is
when the code executes it always gives us Admin as the user. I included the
code below.
Does anyone have any ideas?

TIA
Ted


Private Sub Ctl3__EFFDATE_AfterUpdate()
Dim db As Database
Dim audit As Recordset
Dim garbage As Integer
Set db = OpenDatabase("L:\claims\audit.mdb")
Set audit = db.OpenRecordset("tblRCACMBAudit")
garbage = WriteAuditTrail(audit, Me.Ctl3__EFFDATE.NAME)
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Function WriteAuditTrail(rst As Recordset, fn As String) As Integer
On Error GoTo ErrorHandler
' Edit the current record in the recordset.
rst.AddNew
MsgBox Workspaces(0).UserName
rst!UserModified = Workspaces(0).UserName
rst!claimnumber = Forms![frmRCAClaims]![1#_CLM#]
rst!FormModified = Me.NAME
rst!fieldmodified = fn
rst!DateTimeModified = Now
rst.Update
ErrorHandler:
Select Case Err
Case 0
' conSuccess is defined at the module level as a public constant
' of type Integer with a value of zero.
WriteAuditTrail = conSuccess
Exit Function
Case Else
MsgBox "Error " & Err & ": " & Error, vbOKOnly, "ERROR"
WriteAuditTrail = Err
Exit Function
End Select

End Function

 

[TC]

If access requires each user to log-on with a valid username/password when
they start the database, then, the CurrentUser() function will return the
username that they logged-on with.

If access does *not* require each user to log-on with a valid
username/password when they start the database, then, you have not assigned
a password to the Admin user, so everyone is automatically logged on as
Admin by default. That would explain your problem. It would also call into
question whether you had, in fact, secured the database! (Because assigning
a password to the Admin user is a fundamental step in that process.)

HTH,
TC