Retrieving User ID (1 Viewer)

[music_al]

What is the simplest way to retrieve a User ID using VBA ?

 

[DCrake]

Which users ID are you referring to?

 

[PNGBill]

on my access 2000 you can use CurrentUser() ina query / SQL or vba code to retrieve the current users id.

This can be to load a variable

Dim UserIntials As String
UserInitials = CurrentUser()

or within the vba SQL string

strSQL = "CurrentUser() AS UserIntials.......

say for an Append SQL

 

[music_al]

I have used the piece of code below from PNGBill and it is just returning "Admin" when I open it up on my PC and a number of colleagues PCs.

I was expecting to see their Windows Logon ID.

 

[PaulO]

Try

= Environ("UserName")

 

[gemma-the-husky]

currentuser is the user logged in to access, which is ADMIN by default, unless you use user security.

Environ("UserName"), as pointed out returns the windows login name.

 

[music_al]

Thanks Dave - worked a treat

 

[Galaxiom]

Remember that Environ("UserName") does not actually return the Windows Username but the Environment variable whose default is the Windows username.

Environment variables can be changed so never use Environ("UserName") as a security component. Use an API call to get the actual username from Windows.

 

[Simon_MT]

Alternatively you can use a Function in this case GetWinUserName=Environ("UserName") as the basis of a reference to an Employees Table:

Function GetUser()

Dim db As DAO.Database
Dim rs As DAO.Recordset

    With CodeContextObject
        Set db = CurrentDb
        Set rs = db.OpenRecordset("SELECT EmployeesLookup.Employee, EmployeesLookup.[Employee Login], EmployeesLookup.[Employee Inv Approval] FROM EmployeesLookup WHERE EmployeesLookup.[Employee Login] = '" & GetWinUserName & "'")
        Do
            .UserDb = rs!Employee
            .UserInv = rs![Employee Inv Approval]
            rs.MoveNext
        Loop Until rs.EOF
    
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    End With

End Function

Simon

 

[Galaxiom]

    With CodeContextObject
        Set rs = db.OpenRecordset("SELECT EmployeesLookup........ 
       
        Do
            .UserDb = rs!Employee
            .UserInv = rs![Employee Inv Approval]
        Loop
 
     End With

I have never used the CodeContextObject before but that does not look right. (I have stripped the code down to the issue I see.)

You appear to be setting the CodeContextObject properties to a value from the recordset. This page says the CodeContextObject property is read only which would be expected if I understand it right.

http://msdn.microsoft.com/en-us/library/aa286747(office.10).aspx

The function doesn't seem to return a value either.

Can you either correct the code or explain what I have misunderstood?

Thanks anyway Simon, as it is nice to be introduced to the CodeContextObject. I image it could be quite useful.

 

[Simon_MT]

Galaxiom,

I have an application that requires duality disclosure of prices and without. So there are two sets of one for entry and one for enquiry. So CodeContextObject is a gem as it doesn't care which object is calling the Function, it even works on Subforms. It represents an object, I don't use Sub routines only Functions.

This particular script is called from the Default Form (Menu) to identify the user and picks up the Employees initals and places this information onto the Menu as part of the 'global variables' without declaring global variables for that user. The Menu also locks in the BE database and ensures persistency. I have attached the Menu.

Simon

Application was not my idea!

 

[Galaxiom]

I can see I need to get my head around this because I sense it is powerful. Going to have to use it to understand it.

 

[Simon_MT]

Galaxiom,

I found this by mistake if I remember correctly converting macros to modules. It is shorthand for 'Me' in a Sub but it came into its own when I realised it could be used in subforms, I could never get the syntax right and this worked.

Simon

 

[Tim L]

...Environment variables can be changed so never use Environ("UserName") as a security component. Use an API call to get the actual username from Windows.

Glaxiom,

thank you for bringing this issue to the attention of the wider community. Would you please care to provide at least one example of an API which is commonly available and how it could be implemented (or a link to a suitable article)?

Cheers,

Tim

 

[smig]

and what will you do with this userID ?
unless you work on server managed network it can be changed at any time

 

[DCrake]

Option Compare Database   'Use database order for string comparisons
Option Explicit

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function GetUserID() As String
On Error GoTo GetUserID_Error

'returns the Username of the currently logged in user
    
    Dim strUserName As String * 100
    Dim pLen As Long
    Dim RetVal As Long

    pLen = Len(strUserName)
    RetVal = GetUserName(strUserName, pLen)
    pLen = InStr(strUserName, Chr$(0)) - 1
    GetUserID = Left$(strUserName, pLen)
   
GetUserID_Exit:
   On Error Resume Next
   DBEngine.Idle dbFreeLocks
   DBEngine.Idle dbRefreshCache
   Exit Function
   
GetUserID_Error:
   Dim DisplayMessage As String
   DisplayMessage = Nz(Choose(Application.CurrentObjectType, "Query", "Form", "Report", "Macro", "Module"), "Host")
   DisplayMessage = DisplayMessage & vbTab & ": " & Application.CurrentObjectName & vbNewLine
   DisplayMessage = DisplayMessage & "Event" & vbTab & ": GetUserID" & vbNewLine
   DisplayMessage = DisplayMessage & "Error" & vbTab & ": " & Err & vbNewLine
   DisplayMessage = DisplayMessage & "Text" & vbTab & ": " & Error$ & vbNewLine
   DisplayMessage = DisplayMessage & vbNewLine & "If this error persists please note down these details together with what you were trying to do and call for technical assistance."
   MsgBox DisplayMessage, vbCritical + vbOKOnly, "An error has occured in "
   GetUserID = "ErrorInGetUserName"
   Resume GetUserID_Exit
   
End Function