Retrieve the currentuser from Users table (Acc2007)

[psunilve]

Hello it's me again, an novice access user.



I am trying to set the default value of a textbox for new records to Environ("Username"), I was able to get this to work by using the code below.... with default value set to =fOSUserName()

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

I tried to use the one below with default value set to =getWinUser(), but it didn't work so i decided to stick to the one above
Public Function getWinUser() As String
getWinUser = Environ("UserName")
End Function

I have created a table named "tblTeamMember" that contains the username and the TeamMemberName (e.g. username andysmith, TeamMemberName ANDY SMITH). I want to match the value returned from fOSUserName() with the value that is in TeamMemberName field. Will this code work?
Me!LoggeBy= DLookup("TeamMemberName", "UserName='" & Environ("USERNAME") & "'")

On what event should i put the code in? Please help....

[June7]

I don't use the api method. Environ("UserName") works for me. You tried expression in the DefaultValue property? Don't think that works.

Try just setting value of textbox in form Current event:

If IsNull(Me!LoggedBy) Then Me!LoggedBy = DLookup("TeamMemberName", "tblTeamMember", "UserName='" & Environ("USERNAME") & "'")

[psunilve]

I don't use the api method. Environ("UserName") works for me. You tried expression in the DefaultValue property? Don't think that works.

Try just setting value of textbox in form Current event:

If IsNull(Me!LoggedBy) Then Me!LoggedBy = DLookup("TeamMemberName", "tblTeamMember", "UserName='" & Environ("USERNAME") & "'")

Hi. Thanks once again. I was able to fix the recent issue i posted. After a few tweaks here and there I was able to make it work using this code without the getwinuser function.


Private Sub Form_Current()
If IsNull(Me.Logged_By) Or Trim(Me.Logged_By) = "" Then
Me.Logged_By = DLookup("ADMGName", "ADMFTeamMembers", "UserName='" & Environ("UserName") & "'")
End If
End Sub


But another problem surfaced. I have a field (ID) set as autonumber. We use this field as a reference number when a job is completed. When a user opens the form it goes to the last record. If you click on the Next button or the "Create New Record", the ID is null and will not generate a new number unless one of the fields is filled in. Now since the LoggedBy field has been defaulted to the username, when i click on the Next button, the ID is already generated. Is there a way I can restrict the form from creating a reference number until the record is saved.

Thanks.

[June7]

No, that is the nature of autonumber field. Data entry in other fields initiates record and ID is generated.