Data Macro Audit Trail UserName Error

[GKJR]

I'm trying to finish up an audit trail in Access 2010 (Windows 7 Pro) using data macros. When I first set up my computer in Windows I chose to set the username as my company's name, but then later I changed it to my name in the Windows Control Panel. If I type

? Environ("UserName")

in the immediate window, the system returns the original username I created (my company's name) and not the current username (my name). It isn't that big of a deal because I still know which computer is which, but one of the other computers at my office is now being used by a different employee and they're having the same issue. Does anyone know if there is a simple fix to change the OS User Name that actually gets captured by Access, or is this is a lost cause?

[jimatqsi]

What name are you using to log into Windows? I always thought that was the user name returned by Environ.

Are you on a domain using some Windows Server O/S? Where in the control panel did you change the username?

Jim

[GKJR]

Hi Jim,
I'm not on a server o/s, we have a pretty primitive network.
If you go the control panel in category view, then go to User Accounts and Family Safety, then click User Accounts, Change your account name, that is the only way I could find to change the user name.

[Seth Schrock]

Here is the code that I use to get the Windows user name since the Environ method can be faked.

Private Declare Function GetUserName Lib "advapi32.dll" _
    Alias "GetUserNameA" (ByVal lpBuffer As String, _
                          lpnSize As Long) As Long


Public Function GetUserID()
Dim strUser
Dim s$, cnt&, dl&
Dim max_String As Integer
 
    max_String = 30
    cnt& = 199
    s$ = String$(max_String, 1)
    dl& = GetUserName(s$, cnt&)
    strUser = Trim$(Left$(s$, cnt&))
    strUser = UCase(Mid(strUser, 1, Len(strUser) - 1))

End Function

[jimatqsi]

Seth, that looks like an Article in the making :)

jim

[Seth Schrock]

Its not mine to make. One of the other experts here on Bytes gave it to me. Unfortunately, I'm not sure if this will work of the OP. I have never had the Environ method not work for me so I don't know what is causing it to not work and therefore how this API will work in that situation. Worth a try though.

[GKJR]

I tried the code and it isn't returning anything for me. Why doesn't it set a value to GetUserID() in the function?

[Seth Schrock]

Oops. I had to extract some code that I had added and accidently removed some necessary code. Add the following on line 17

GetUserID = strUser

I actually create a tempvar so that I don't have to call my function every single time I need the username.

[GKJR]

It is giving me the same result but everything is in caps now. Thanks for the effort Seth. I'm starting to think there are two separate system fields, one for User Name and the other for Display Name, or something to that effect.

[Seth Schrock]

To get the lower case, just use the LCase function on the strUser variable. The other part I can't help you with.

[Luk3r]

I actually found the Microsoft article that outlines doing this. The major difference between the article and what Seth offered is the .dll that the API uses. May try giving it a shot :)

http://support.microsoft.com/kb/161394

[GKJR]

Thanks Luk3r but still no luck. It is returning the same value.

[GKJR]

So as a test I just created a new account on my computer and made some changes to my audited fields. My original process recorded the correct username this time. I then changed that username and made more changes to my audited fields. My audit table recorded the original username from before I changed it. So I don't know if there is some other answer to make this work correctly, but at least I know how to get the name I need. I have to set up a new account and just get the name right when creating it.

[zmbd]

More than likely there is a typo the keeps getting passed on.

So when all else fails, back to the original BASIC code:
This is based upon three different sources, I pulled out all of the extra crud and fancy stuff and converted to function.

Personally, I call the function each time I need to vet the current user. I have this in a current production application that runs daily over the past 10 years.

The following code has been vetted on:
WinXP (sp1 - sp3)
WinXP-Pro (sp1 - sp3)
WinXP-MCE (sp1 - sp3
WinVista
Win7-Home-Premium-64bit
Win7-Enterprise-64bit

Office2003 in all the above OS
Office2010(32bi t) in all of the above OS

In each case, the returned value is either the network windows user authentication name or the user name entered at the user name and password login screen (ctrl-alt-del)

Insert a new module and the copy the following.

Option Compare Database
Option Explicit
'you only need the above two lines once in the module

Private Declare Function apiGetUserName _
   Lib "advapi32.dll" Alias _
   "GetUserNameA" _
   (ByVal lpBuffer As String, nSize As Long) As Long

Function zFNC_UserLogedIn() As String
   ' Returns the network login name
   Dim zlngLen As Long
   Dim zX As Long
   Dim zstrUserName As String
   zstrUserName = String$(254, 0)
   zlngLen = 255
   zX = apiGetUserName(zstrUserName, zlngLen)
   If (zX > 0) Then
        zFNC_UserLogedIn = Left$(zstrUserName, zlngLen - 1)
    Else
        zFNC_UserLogedIn = vbNullString
    End If
End Function

press <ctrl><g>
type ?zFNC_UserLoged In
in the immediates window.
Let us know what happens

(did you catch the punny at the begining of the post ? ROTFL)

BTW: Had to reset your answer.

So I don't know if there is some other answer to make this work correctly, but at least I

This indicates that the root cause is still unknown and that a better answer may be out there...

[GKJR]

It still returns the original username, not the edited one...