Capturing UserName and Populating a Name Field

[tpeter]

I am new to both Access 2003 and VBA and need some help. I have a database I am building and would like to autopopulate the Name controls with the current user that is logged in. I have got the code to capture the current user from the Windows login and made some changes to it. The modual with the GetCurrentUserName= "Tim Peter" and in the form I have a GetCurrentUserID(if GetCurrentUserName is Tim Peter then Get CurrentUserID is 18). I need to find out how to associate the GetCurrentUserID to my Userid in my employee table and populate the name box. Any help would be appreciated.

Here is the GetCurrentUserName:

Option Compare Database
Private Declare Function GetUserName Lib "ADVAPI32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long

Public Function GetCurrentUserName() As String
On Error GoTo Err_GetCurrentUserName
Dim lpBuff As String * 25
Dim ret As Long, UserName As String
ret = GetUserName(lpBuff, 25)

UserName = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
GetCurrentUserName = UserName & ""

If GetCurrentUserName = "tpeter" Then
GetCurrentUserName = "Tim Peter"

ElseIf GetCurrentUserName = "tramage" Then
GetCurrentUserName = "Tom Ramage"

ElseIf GetCurrentUserName = "bbrubacher" Then
GetCurrentUserName = "Bob Brubacher"

ElseIf GetCurrentUserName = "wdekock" Then
GetCurrentUserName = "Wayne DeKock"


End If

Exit_GetCurrentUserName:
Exit Function

Err_GetCurrentUserName:
MsgBox Err.Description
Resume Exit_GetCurrentUserName
End Function



Public Function GetCurrentUserID()

If GetCurrentUserName = "Tim Peter" Then
GetCurrentUserID = "18"
End If



End Function


The database is split but the name of the table that has the UserID is [tblemployees]

Thank you for your help.

 

[Vbark]

In Default of the username field you can put

=Environ("UserName")

to return the nt login, this can also be used in a function like this

Public Function GetUserName() as String

GetUserName =Environ("UserName")


End Function

 

[Vbark]

Sorry I re-read your message.

What I normally do with this is, have a form that runs on the database open linked to the tblemployees table

logic being someting like

Private Sub Form_Open(Cancel As Integer)

Dim RetVal          As Long
    
RetVal = Nz(DLookup("UserID", "tblemployees", "FieldThatHasNTLogin = '" & Environ("UserName") & "'"), -1)

'Nz function places a value if a null is returned in this case I have asked for a -1 to be returned being a value that would not be found in the userid field

If RetVal = -1 Then
    MsgBox "user Does not exist"
    'close db or something
    DoCmd.Quit acQuitSaveAll
    Else ' otherwise filter the form based on the userid
    Me.Filter = "UserID=" & RetVal
    Me.FilterOn = True
    End If

End Sub

 

[tpeter]

I put this code into the on enter event of the form and it is cumming up with a false of the filter and closing the database. The FieldThatHasNTLogin is not located in the empoyee table is that a problem? The form that contains the NTLogin is frmLogin and the control is LoginName. The form has no table associated with it only visual and never stored.


Private Sub Name_Enter()
Private Sub Form_Open(Cancel As Integer)

Dim RetVal As Long

RetVal = Nz(DLookup("UserID", "tblemployee", "LoginName" = "" & Environ("UserName") & "'"), -1)

'Nz function places a value if a null is returned in this case I have asked for a -1 to be returned being a value that would not be found in the userid field

If RetVal = -1 Then
MsgBox "user Does not exist"
'close db or something
DoCmd.Quit acQuitSaveAll
Else ' otherwise filter the form based on the userid
Me.Filter = "UserID=" & RetVal
Me.FilterOn = True
End If

End Sub

 

[Vbark]

Where to start

* Your employee table should contain a field that contains the nt login you will need to add it if not there, this is used to look up the record that you require, call it what you want and replace the 'NTLoginField' with what you called it.

* Your form should have a data source referenceing back to the tblemployee table so you can filter the records based on the found ntuser login field

* the DoCmd.Quit acQuitSaveAll line is optional remove it if you want

* The above could be made simpler by simply using

Me.Filter = "NTLoginField='" & Environ("Username") & "'"
Me.FilterOn = True

But then you cannot see if the user exists

*This bit is wrong it is one or the other not both...

Private Sub Name_Enter()
Private Sub Form_Open(Cancel As Integer)

 

[tpeter]

Alright I am very sorry for being a pain in ______. But it doesn't seem to want to work. I have tried this in my standard modual that gets the current users name and in the form and nothing happens:

Private Sub EnterName()


Dim RetVal As Long

RetVal = Nz(DLookup("UserID", "tblemployee", "NTLoginField" = "" & Environ("UserName") & "'"), -1)

'Nz function places a value if a null is returned in this case I have asked for a -1 to be returned being a value that would not be found in the userid field

If RetVal = -1 Then
MsgBox "user Does not exist"
'close db or something
DoCmd.Quit acQuitSaveAll
Else ' otherwise filter the form based on the userid
Me.Filter = "NTLoginField=" & RetVal
Me.FilterOn = True
End If

When This is used nothing happens.I have added the field to the employee table of NTLoginField and entered tpeter(this is what is returned from the windows login)

I have also just tryed the me function in both areas and nothing happens. Here is the SQL from the name field I am trying to populate:

 

[tpeter]

Here it is:
SELECT tblemployee.UserId, tblemployee.FirstName & " " & tblemployee.LastName AS Expr1
FROM tblemployee;

Once again I am sorry for being a pain.

Tim

 

[Vbark]

If you give me your email address I will send you a working copy.

 

[Vbark]

View attachment GetUserName.zip try this