By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,654 Members | 1,575 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,654 IT Pros & Developers. It's quick & easy.

Data Macro Audit Trail UserName Error

100+
P: 104
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
Expand|Select|Wrap|Line Numbers
  1. ? 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?
Apr 30 '14 #1
Share this Question
Share on Google+
21 Replies


Expert 100+
P: 1,240
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
Apr 30 '14 #2

100+
P: 104
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.
Apr 30 '14 #3

Seth Schrock
Expert 2.5K+
P: 2,951
Here is the code that I use to get the Windows user name since the Environ method can be faked.
Expand|Select|Wrap|Line Numbers
  1. Private Declare Function GetUserName Lib "advapi32.dll" _
  2.     Alias "GetUserNameA" (ByVal lpBuffer As String, _
  3.                           lpnSize As Long) As Long
  4.  
  5.  
  6. Public Function GetUserID()
  7. Dim strUser
  8. Dim s$, cnt&, dl&
  9. Dim max_String As Integer
  10.  
  11.     max_String = 30
  12.     cnt& = 199
  13.     s$ = String$(max_String, 1)
  14.     dl& = GetUserName(s$, cnt&)
  15.     strUser = Trim$(Left$(s$, cnt&))
  16.     strUser = UCase(Mid(strUser, 1, Len(strUser) - 1))
  17.  
  18. End Function
Apr 30 '14 #4

Expert 100+
P: 1,240
Seth, that looks like an Article in the making :)

jim
Apr 30 '14 #5

Seth Schrock
Expert 2.5K+
P: 2,951
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.
Apr 30 '14 #6

100+
P: 104
I tried the code and it isn't returning anything for me. Why doesn't it set a value to GetUserID() in the function?
Apr 30 '14 #7

Seth Schrock
Expert 2.5K+
P: 2,951
Oops. I had to extract some code that I had added and accidently removed some necessary code. Add the following on line 17
Expand|Select|Wrap|Line Numbers
  1. GetUserID = strUser
I actually create a tempvar so that I don't have to call my function every single time I need the username.
Apr 30 '14 #8

100+
P: 104
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.
Apr 30 '14 #9

Seth Schrock
Expert 2.5K+
P: 2,951
To get the lower case, just use the LCase function on the strUser variable. The other part I can't help you with.
Apr 30 '14 #10

100+
P: 300
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
Apr 30 '14 #11

100+
P: 104
Thanks Luk3r but still no luck. It is returning the same value.
Apr 30 '14 #12

100+
P: 104
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.
Apr 30 '14 #13

zmbd
Expert Mod 5K+
P: 5,397
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.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. 'you only need the above two lines once in the module
  4.  
  5. Private Declare Function apiGetUserName _
  6.    Lib "advapi32.dll" Alias _
  7.    "GetUserNameA" _
  8.    (ByVal lpBuffer As String, nSize As Long) As Long
  9.  
  10. Function zFNC_UserLogedIn() As String
  11.    ' Returns the network login name
  12.    Dim zlngLen As Long
  13.    Dim zX As Long
  14.    Dim zstrUserName As String
  15.    zstrUserName = String$(254, 0)
  16.    zlngLen = 255
  17.    zX = apiGetUserName(zstrUserName, zlngLen)
  18.    If (zX > 0) Then
  19.         zFNC_UserLogedIn = Left$(zstrUserName, zlngLen - 1)
  20.     Else
  21.         zFNC_UserLogedIn = vbNullString
  22.     End If
  23. 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...
Apr 30 '14 #14

100+
P: 104
It still returns the original username, not the edited one...
Apr 30 '14 #15

zmbd
Expert Mod 5K+
P: 5,397
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... (^_^) )
May 1 '14 #16

zmbd
Expert Mod 5K+
P: 5,397
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:

Expand|Select|Wrap|Line Numbers
  1. Sub getadinfo()
  2.     Dim zsys As Object
  3.     Dim zsysuser As Object
  4.     On Error GoTo zerrtrap
  5.     Set zsys = CreateObject("ADSystemInfo")
  6.     Set zsysuser = GetObject("LDAP://" & zsys.UserName)
  7.     'Dim sys As New ADSystemInfo
  8.     Debug.Print "Display: " & zsysuser.displayname
  9.     Debug.Print "Display: " & zsysuser.mail
  10.     Debug.Print "Display: " & zsysuser.samAccountName
  11.     Debug.Print "User: " & zsys.UserName
  12.     Debug.Print "Computer: " & zsys.ComputerName
  13.     Debug.Print "NativeMode: " & zsys.isnativemode
  14.     Debug.Print "Domain: " & zsys.DomainDNSName
  15.     Debug.Print "PDC Role Owner: " & zsys.PDCRoleOwner
  16.     'cleanup
  17.     If Not zsysuser Is Nothing Then Set zsysuser = Nothing
  18.     If Not zsys Is Nothing Then Set zsys = Nothing
  19.     'prompt
  20.     MsgBox "chek for any information, close this prompt and press <ctrl><g>"
  21.     Exit Sub
  22. zerrtrap:
  23.     Debug.Print Err.Number, Err.Description
  24.     Resume Next
  25. 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...
May 1 '14 #17

Seth Schrock
Expert 2.5K+
P: 2,951
Thanks for that Z. It will come in handy. A few typos in it though.

Expand|Select|Wrap|Line Numbers
  1. Line 9 - sys.UserName needs to be zsys.UserName
  2. Line 20 - zSet sysuser = nothing needs to be Set zsysuser = Nothing
May 1 '14 #18

zmbd
Expert Mod 5K+
P: 5,397
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.
May 1 '14 #19

100+
P: 104
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:
Expand|Select|Wrap|Line Numbers
  1.     Debug.Print "1"
  2.     Debug.Print "Display: " & zsysuser.displayname
  3.     Debug.Print "2"
  4.     Debug.Print "Display: " & zsysuser.mail
  5.     Debug.Print "3"
  6.     Debug.Print "Display: " & zsysuser.samAccountName
  7.     Debug.Print "4"
  8.     Debug.Print "User: " & zsys.UserName
  9.     Debug.Print "5"
  10.     Debug.Print "Computer: " & zsys.ComputerName
  11.     Debug.Print "6"
  12.     Debug.Print "NativeMode: " & zsys.isnativemode
  13.     Debug.Print "7"
  14.     Debug.Print "Domain: " & zsys.DomainDNSName
  15.     Debug.Print "8"
  16.     Debug.Print "PDC Role Owner: " & zsys.PDCRoleOwner
  17.  
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.
May 1 '14 #20

zmbd
Expert Mod 5K+
P: 5,397
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.
May 1 '14 #21

100+
P: 104
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.
May 1 '14 #22

Post your reply

Sign in to post your reply or Sign up for a free account.