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

Retrieve User ID

P: 16
How can I retrieve the user id of the user signed on in access. I would like to use this to mark records added or changed by user.
Oct 19 '06 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,307
I'll try to write this all out again so that it makes sense.
I did it earlier and it fell into the bit-bucket on posting.

Basically the Registry has 2 places it stores this info - depending on the basic OS (Old Windows or NT Windows - which includes ALL later versions).
This is illustrated in the function below.

To get this to work I had to set up a separate module with OS access functions & utilities in.
This may or may not be necessary in your environment (you may already have something similar), but in case it is, I've included the code for the module (I've called it modOS) in this post.

I can't guarantee all the code in this post but it is free to use and I use it fairly heavily with no noticed side-effects so far.

If you don't want to use any of the code it should at least illustrate what is available to you.
FYI. In NT based OSs the user name is also available in the Environment as USERNAME.


Expand|Select|Wrap|Line Numbers
  1. 'GetUser returns the user's logged on name.
  2. Public Function GetUser() As String
  3.     Dim strUserKey As String
  4.  
  5.     If Environ("OS") = "Windows_NT" Then
  6.         strUserKey = "SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon"
  7.         GetUser = RegRead(conHKLM, strUserKey, "DefaultUserName")
  8.     Else
  9.         'Windows
  10.         strUserKey = "Network\Logon"
  11.         GetUser = RegRead(conHKLM, strUserKey, "username")
  12.     End If
  13. End Function

Expand|Select|Wrap|Line Numbers
  1. modOS
  2. Option Compare Database
  3. Option Explicit
  4.  
  5. Public Const conHKCR = &H80000000
  6. Public Const conHKCU = &H80000001
  7. Public Const conHKLM = &H80000002
  8. Public Const conHKU = &H80000003
  9. Public Const conStandardRightsAll = &H1F0000
  10. Public Const conReadControl = &H20000
  11. Public Const conStandardRightsRead = (conReadControl)
  12. Public Const conRegSz = 1
  13. Public Const conOK = 0&
  14. Public Const conKeyQueryValue = &H1
  15. Public Const conKeySetValue = &H2
  16. Public Const conKeyCreateLink = &H20
  17. Public Const conKeyCreateSubKey = &H4
  18. Public Const conKeyEnumerateSubKeys = &H8
  19. Public Const conKeyNotify = &H10
  20. Public Const conSynchronise = &H100000
  21. Public Const conRegOptionNonVolatile = 0
  22. Public Const conKeyAllAccess = ((conStandardRightsAll Or _
  23.                                 conKeyQueryValue Or _
  24.                                 conKeySetValue Or _
  25.                                 conKeyCreateSubKey Or _
  26.                                 conKeyEnumerateSubKeys Or _
  27.                                 conKeyNotify Or _
  28.                                 conKeyCreateLink) And _
  29.                                (Not conSynchronise))
  30. Public Const conKeyRead = ((conReadControl Or _
  31.                             conKeyQueryValue Or _
  32.                             conKeyEnumerateSubKeys Or _
  33.                             conKeyNotify) And _
  34.                            (Not conSynchronise))
  35.  
  36. Private Declare Function RegOpenKeyEx Lib "advapi32.dll" _
  37.     Alias "RegOpenKeyExA" (ByVal hKey As Long, _
  38.                            ByVal lpSubKey As String, _
  39.                            ByVal ulOptions As Long, _
  40.                            ByVal samDesired As Long, _
  41.                            phkResult As Long) As Long
  42. Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) _
  43.                              As Long
  44. Private Declare Function RegQueryValueExStr Lib "advapi32.dll" _
  45.     Alias "RegQueryValueExA" (ByVal hKey As Long, _
  46.                               ByVal lpValueName As String, _
  47.                               ByVal lpReserved As Long, _
  48.                               lpType As Long, _
  49.                               ByVal lpData As String, _
  50.                               lpcbData As Long) As Long
  51.  
  52. Public Function RegRead(ByVal lngHive As Long, _
  53.                         ByVal strKey As String, _
  54.                         ByVal strValue As String) As Variant
  55.     Dim intIdx As Integer, intHK As Integer
  56.     Dim strWork As String
  57.     Dim lngRet As Long, lngLen As Long, lngHKey As Long, lngType As Long
  58.  
  59.     RegRead = Null
  60.     strKey = strKey & Chr(0)
  61.     lngRet = RegOpenKeyEx(lngHive, strKey, 0, conKeyRead, lngHKey)
  62.     If lngRet = conOK Then
  63.         'Create buffer to store value
  64.         strWork = Space(255)
  65.         lngLen = 255
  66.         lngRet = RegQueryValueExStr(lngHKey, _
  67.                                     strValue, _
  68.                                     0&, _
  69.                                     lngType, _
  70.                                     strWork, _
  71.                                     lngLen)
  72.         RegRead = Left(strWork, lngLen - 1)
  73.         If Len(RegRead) = 254 Then RegRead = Null
  74.         'Close key
  75.         Call RegCloseKey(lngHKey)
  76.     End If
  77. End Function
Oct 19 '06 #2

100+
P: 143
Use this code in a module.
Expand|Select|Wrap|Line Numbers
  1. Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
  2.  
  3.  
  4. Public Function GetNTUser() As String
  5.     'Returns the network login name
  6. Dim strUserName As String
  7.     'Create a buffer
  8. strUserName = String(100, Chr$(0))
  9.     'Get user name
  10. GetUserName strUserName, 100
  11.     'Strip the rest of the buffer
  12. strUserName = Left$(strUserName, InStr(strUserName, Chr$(0)) - 1)
  13. GetNTUser = strUserName
  14. End Function
In your table or tables, add another field, UserID In the Before Update event of the form on which your table is based, add this code. This assumes yourDB is networked.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. Me!UserID = GetNTUser & " " & Date
  3. End Sub
Oct 19 '06 #3

P: 8
How can I retrieve the user id of the user signed on in access. I would like to use this to mark records added or changed by user.
There is an in built function for this in Access "currentUser()"

eg:

MsgBox("The current user is: " & CurrentUser)
Oct 19 '06 #4

NeoPa
Expert Mod 15k+
P: 31,307
CurrentUser() will return the Access Security account used rather than the PC or Network LogOn name.
If you use this to identify a user that will work well. Unfortunately, in many environments, this will return Admin.
Oct 19 '06 #5

P: 1
Dear Tanis ,
Your code has worked perfectly for me. Thank you very much!
Sep 8 '08 #6

NeoPa
Expert Mod 15k+
P: 31,307
As this thread was started and answered on my first day as a member of The Scripts Developer Network (as was Bytes then), I'm very pleased to see that it's still able to help people, some years later.

Thank you for your post, and welcome to Bytes!
Sep 8 '08 #7

Post your reply

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