Generally, I would use the user's LAN ID to identify them to the Access database, rather than having them enter their ID or their name.
Here is some code that I use to determine the LAN setup, before coding the Access routine. Put this code in a standard module, then type two commands in the immediate window -
"Call ShowUserNames()" will show you how your installation formats the user names. Be aware that, if the machines on your LAN have two or more different operating systems (Win XT, Win 7, Win 8) that they will not usually all agree on all these name formats.
"Call ShowEnvironmentVariables()" will show you what other environment variables are available on your installation. Again, if the machines on your LAN have two or more different operating systems (Win XT, Win 7, Win 8) that they will not usually all agree on all these variables.
Between the two of those, you should be able to determine a set of variables that you can query to identify the person logged onto your database by their LAN logon. Obviously, if you're on a heterogenous network, you'll have to execute those commands on a few different machines to find which variables are stable enough to use.
Code:
Option Compare Database
Option Explicit
' This code is courtesy of TheChazm, posted on a thread at
' http://www.access-programmers.co.uk/....php?p=1077222
Private Enum EXTENDED_NAME_FORMAT
NameUnknown = 0
NameFullyQualifiedDN = 1
NameSamCompatible = 2
NameDisplay = 3
NameUniqueId = 6
NameCanonical = 7
NameUserPrincipal = 8
NameCanonicalEx = 9
NameServicePrincipal = 10
End Enum
Private Declare Function GetUserNameExA Lib "secur32.dll" _
(ByVal NameFormat As EXTENDED_NAME_FORMAT, ByVal lpNameBuffer As String, _
ByRef nSize As Long) As Long
Function GetUserNameExt(Optional ByVal pNameFormat As Integer = NameSamCompatible) As String
Dim sBuffer As String, Ret As Long
sBuffer = String(256, 0)
Ret = Len(sBuffer)
If GetUserNameExA(pNameFormat, sBuffer, Ret) <> 0 Then
GetUserNameExt = Left$(sBuffer, Ret)
Else
GetUserNameExt = "ERROR"
End If
End Function
' This code is courtesy of Dal Jeanis.
' You are free to use and redistribute it as desired.
Public Function ShowUserNames() As String
Debug.Print " * "
Debug.Print " * **** STARTING USER NAMES EXTENDED ***** "
Debug.Print " * "
Debug.Print "NameUnknown = " & GetUserNameExt(NameUnknown)
Debug.Print " * "
Debug.Print "NameFullyQualifiedDN = " & GetUserNameExt(NameFullyQualifiedDN)
Debug.Print " * "
Debug.Print "NameSamCompatible = " & GetUserNameExt(NameSamCompatible)
Debug.Print " * "
Debug.Print "NameDisplay = " & GetUserNameExt(NameDisplay)
Debug.Print " * "
Debug.Print "4 = " & GetUserNameExt(4)
Debug.Print " * "
Debug.Print "5 = " & GetUserNameExt(5)
Debug.Print " * "
Debug.Print " * **** MIDDLE USER NAMES EXTENDED ***** "
Debug.Print " * "
Debug.Print "NameUniqueId = " & GetUserNameExt(NameUniqueId)
Debug.Print " * "
Debug.Print "NameCanonical = " & GetUserNameExt(NameCanonical)
Debug.Print " * "
Debug.Print "NameUserPrincipal = " & GetUserNameExt(NameUserPrincipal)
Debug.Print " * "
Debug.Print "NameCanonicalEx = " & GetUserNameExt(NameCanonicalEx)
Debug.Print " * "
Debug.Print "NameServicePrincipal = " & GetUserNameExt(NameServicePrincipal)
Debug.Print " * "
Debug.Print " * **** ENDING USER NAMES EXTENDED ***** "
Debug.Print " * "
Debug.Print " * "
Debug.Print " * **** STARTING ENVIRON INFORMATION ***** "
Debug.Print " * "
Debug.Print " USERID = " & Environ("UserID")
Debug.Print " * "
Debug.Print " USERNAME = " & Environ("Username")
Debug.Print " * "
Debug.Print " COMPUTERNAME = " & Environ("Computername")
Debug.Print " * "
Debug.Print " USERPROFILE = " & Environ("Userprofile")
Debug.Print " * "
Debug.Print " HOMEPATH = " & Environ("Homepath")
Debug.Print " * "
Debug.Print " OS = " & Environ("OS")
Debug.Print " * "
ShowUserNames = "DONE"
End Function
Function ShowEnvironmentVariables()
Dim EnvString, Indx, Msg, PathLen ' Declare variables.
Dim varRet As Variant
varRet = SysCmd(acSysCmdAccessVer)
Debug.Print " * "
Debug.Print " * *** ACQUIRING ACCESS VERSION *** * "
Debug.Print " * "
Debug.Print " * Your MS Access Version Is " & Application.Version
Debug.Print " * Your MS Access Syscmd Version Is " & varRet
Debug.Print " * V11.0 is 2003, V12.0 is 2007, V14.0 is 2010, V15.0 is 2013"
Debug.Print " * "
Debug.Print " * Your MS Access Build / SP Is " & SysCmd(715)
Debug.Print " * "
Debug.Print " * Your MS Access Syscmd Version Is " & SysCmd(acSysCmdRuntime)
Debug.Print " * "
Indx = 0 ' Initialize index.
Debug.Print " * "
Debug.Print " * *** STARTING ENVIRONMENT VARIABLES *** * "
Debug.Print " * "
Do
Indx = Indx + 1
' Get environment variable.
EnvString = Environ(Indx)
' print value
Debug.Print " * "
Debug.Print " * " & Indx & " * " & EnvString
Loop Until EnvString = ""
Debug.Print " * "
Debug.Print " * *** ENDING ENVIRONMENT VARIABLES *** * "
Debug.Print " * "
ShowEnvironmentVariables = "DONE"
End Function