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(32bit) 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_UserLogedIn
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...

[zmbd]

Ok,
That's what I was thingking was happening; however, I had to make sure by giving you code that I knew was validated.

Short and sweet: You are right
Windows has a user account name
AND
Windows has a friendly name

IE: On my company's domain, I have a user login that has several letters and numbers. Almost impossible to remember let alone guess. HOWEVER, on the PC that I use most often (at my desk) my IT-Dept allowed me to set a friendlier name (which is my work nickname which is unlikely to be guessed).

- If I use the code that I gave you in my post, it will pull the actual network/OS-authenticated user account name.

- This is also the name you will find all of that user's profile information under on the harddrive.

THIS IS BY DESIGN
See, I could change my friendly name to, say my direct-supervisor's user name; however, when I login, I will be authenticated by the OS-UserAccount-profile (mine) and not my managers.

Now, why you would want the user alias I'm not sure; however, I'm sure that now you can see that such is not a "secure" name.

As to how to get reliably the user alias... I'll have to look into that or maybe TheSmileyCoder will have a method

(it this cross posts my applogies, I was called away in the middle of typing this... (^_^) )

[zmbd]

GKJR;
Thinking this maybe an api thing I started digging thru things looking for API or WMI calls. IN the Windows Developer site I ran across the active directory object which doesn't sound like it applies in your case as it sounds more like an adhoc-workgroup network; however, open a new VBA module in the editor and insert the following:

Sub getadinfo()
    Dim zsys As Object
    Dim zsysuser As Object
    On Error GoTo zerrtrap
    Set zsys = CreateObject("ADSystemInfo")
    Set zsysuser = GetObject("LDAP://" & zsys.UserName)
    'Dim sys As New ADSystemInfo
    Debug.Print "Display: " & zsysuser.displayname
    Debug.Print "Display: " & zsysuser.mail
    Debug.Print "Display: " & zsysuser.samAccountName
    Debug.Print "User: " & zsys.UserName
    Debug.Print "Computer: " & zsys.ComputerName
    Debug.Print "NativeMode: " & zsys.isnativemode
    Debug.Print "Domain: " & zsys.DomainDNSName
    Debug.Print "PDC Role Owner: " & zsys.PDCRoleOwner
    'cleanup
    If Not zsysuser Is Nothing Then Set zsysuser = Nothing
    If Not zsys Is Nothing Then Set zsys = Nothing
    'prompt
    MsgBox "chek for any information, close this prompt and press <ctrl><g>"
    Exit Sub
zerrtrap:
    Debug.Print Err.Number, Err.Description
    Resume Next
End Sub

You need only report the error messages that appear in the immediates window... I do not need any of the information this code may provide.

And although it may not solve your issue, it certainly solved one of mine... now I can completely automate the user profile creation by pre-populating the newuser form with friendly name, email, etc...

[Seth Schrock]

Thanks for that Z. It will come in handy. A few typos in it though.

Line 9 - sys.UserName needs to be zsys.UserName
Line 20 - zSet sysuser = nothing needs to be Set zsysuser = Nothing

[zmbd]

That's what I get for typing it in by hand instead of using the editor.
Went back in and fixed the errors in the code.

[GKJR]

Hi z,
I can't make any sense out of what I got in the immediate window so I'm going to paste it all right here. I added a counter right before each of the existing debug.print lines because I was having a hard time following it.

This is what I added to be clear:

    Debug.Print "1"
    Debug.Print "Display: " & zsysuser.displayname
    Debug.Print "2"
    Debug.Print "Display: " & zsysuser.mail
    Debug.Print "3"
    Debug.Print "Display: " & zsysuser.samAccountName
    Debug.Print "4"
    Debug.Print "User: " & zsys.UserName
    Debug.Print "5"
    Debug.Print "Computer: " & zsys.ComputerName
    Debug.Print "6"
    Debug.Print "NativeMode: " & zsys.isnativemode
    Debug.Print "7"
    Debug.Print "Domain: " & zsys.DomainDNSName
    Debug.Print "8"
    Debug.Print "PDC Role Owner: " & zsys.PDCRoleOwner
 

This is everything from the immediate window. The first error was thrown before anything I added.
-2147023564 Automation error
No mapping between account names and security IDs was done.
1
91 Object variable or With block variable not set
2
91 Object variable or With block variable not set
3
91 Object variable or With block variable not set
4
-2147023564 Automation error
No mapping between account names and security IDs was done.
5
-2147023545 Automation error
Configuration information could not be read from the domain controller, either because the machine is unavailable, or access has been denied.
6
-2147023541 The specified domain either does not exist or could not be contacted.

7
-2147023541 Automation error
The specified domain either does not exist or could not be contacted.
8
-2147023541 The specified domain either does not exist or could not be contacted.

[zmbd]

Means that you are not using an active directory (LADP) server in the network to handle your user authentication.
I'll have to figure out where they are storing the displayname on adhoc/workgroupped networked PC's.

[GKJR]

Like I said we have a very primitive network, and to be honest I don't know much about networking otherwise I would address that. As far as I know we have a peer-to-peer network if that helps.