Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Getting all types of Usernames in Access

VBA and Custom Functions

Getting all types of Usernames in Access

by  psemianonymous  Posted    (Edited  )
[ol]
[li]Access/JET security logon
[li]Local Windows logon
[li]Windows Domain server/Novell NetWare logon (may be different from Windows logon)
[li]Computer name
[li]Using environment variables to retrieve the username
[li]A point about case sensitivity
[/ol]

1. Access/JET security logon

Use the CurrentUser() function to return the currently-logged on user to your database workgroup. If the database is not using Access/JET security, the default user is "Admin", and thus CurrentUser() will return "Admin".

example:
Code: 
    txtCreatedBy.Value = CurrentUser()

2. Local Windows logon

This returns the Windows username for the local machine. This is not necessarily the same value as the network logon ID, though most of the time it is.

To get this value, you must make an API call to grab the value. One implementation that neatly takes care of the API declarations and coding is available at the Access MVP web site:

http://www.mvps.org/access/api/api0008.htm

example:
Code: 
    txtCreatedBy.Value = fOSUserName()

NOTE: Please do not be confused by their confusing titles and page headers. This returns the local Windows username, not the network username. See the following newsgroup thread for an example of when the local username and network username are different: http://groups.google.com/groups?th=6e618210e62275b&seekm=Xns93CDD96176A8Edfurmancloud99%40199.184.165.241#link1


3. NT/Netware logon
This returns the network username via an API call. You may paste the following code into a new module and use the apiNetUserName() function.
Code: 
'-----------begin copying below-----------------
Option Explicit

'source: http://groups.google.com/groups?selm=387490BE.1CD2C4D3%40barneyboller.com
Public Declare Function WNetGetUser Lib "mpr.dll" _
        Alias "WNetGetUserA" (ByVal lpName As String, _
        ByVal lpUserName As String, lpnLength As Long) As Long

    Const NoError = 0       'The Function call was successful

Public Function apiNetUserName() As String

        ' Buffer size for the return string.
        Const lpnLength As Integer = 255
        ' Get return buffer space.
        Dim status As Integer
        ' For getting user information.
        Dim lpName, lpUserName As String
        ' Assign the buffer size constant to lpUserName.
        lpUserName = Space$(lpnLength + 1)
        ' Get the log-on name of the person using product.
        status = WNetGetUser(lpName, lpUserName, lpnLength)
        ' See whether error occurred.
        If status = NoError Then
           ' This line removes the null character. Strings in C are null-
           ' terminated. Strings in Visual Basic are not null-terminated.
           ' The null character must be removed from the C strings to be used
           ' cleanly in Visual Basic.
           lpUserName = Left$(lpUserName, InStr(lpUserName, Chr(0)) - 1)
        Else
           ' An error occurred.
           lpUserName = ""
           End
        End If
        FindNetUserName = lpUserName
End Function


'-----------------stop here---------------

example:
Code: 
    txtCreatedBy.Value = apiNetUserName()

4. Computer name
This is also handled via an API call, and the excellent Access MVPs web site has this available as well:

http://www.mvps.org/access/api/api0009.htm


example:
Code: 
    txtComputerName.Value = fOSMachineName()

5. Using environment variables
By far the simplest method to return the username, you may simply use the function Environ("USERNAME") to retrieve the Windows username.

example:
Code: 
    txtCreatedBy.Value = Environ("USERNAME")


This can be problematic, however. Some operating systems do not automatically set this environment variable (Windows 95, 98, and ME). Additionally, users can change environment variables with a simple DOS command. To try this yourself, type
Code: 
C:\>SET USERNAME=BILLG
and now Environ("USERNAME") will return "BILLG" until you set the environment variable to something else or reboot the machine.


6. Case-sensitivity

Note that depending on how you type in your username when logging in, you may get your username returned as "billg" or "Billg" or "BILLG". If you wish, wrap your function calls with UCase() or LCase() to ensure that you get your username formatted as you like.

This way, if you are comparing the current user to a table of users, you can directly check
Code: 
strSQL = strSQL & [USERID] = " & Chr(34) & UCase(CurrentUser()) & Chr(34)
instead of using the "LIKE" operator.
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top