Retrieving current user from a user table (Acc2007)

[vspsdca]

Hello, just a heads up i am a novice in using vba. So i apologise if i dont sound techy.

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....

 

[Solo712]

Hello, just a heads up i am a novice in using vba. So i apologise if i dont sound techy.

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

Try WinUser = VBA.Environ("UserName"). That should work.

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") & "'")

Me!LoggeBy= Nz(DLookup("TeamMemberName", "tblTeamMember", "UserName='" & WinUser & "'"))

There was a middle argument missing. Also, you should protect against Null result with the Nz() function.

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

You would have to tell us more about the purpose of this activity. Are you simply trying to authenticate the user ? Or is the TeamMemberName used in processing of some sorts ?

 

[vspsdca]

Thank you Solo712, the environ("username") worked and it did retrieve the team member name from tblTeamMember.

Ok here's what i am actually doing, I have a form (workbank) that we use to log change requests. Every team member has to log it in the workbank when they have completed the request hence the username feature was added. You know instead a combo box, it defaults to who is logged in. But here is another problem i just discovered... i asked 2 team members to try out the form. They opened it from their machines, Logged By field defaulted to their names, filled in the rest of the fields and saved the record. Then when I opened the form from my machine and opened the underlying table, the records they created had my name instead of theirs. I think it overwrites it by user that is logged in. Any idea why that happens?

 

[Solo712]

Thank you Solo712, the environ("username") worked and it did retrieve the team member name from tblTeamMember.

Ok here's what i am actually doing, I have a form (workbank) that we use to log change requests. Every team member has to log it in the workbank when they have completed the request hence the username feature was added. You know instead a combo box, it defaults to who is logged in. But here is another problem i just discovered... i asked 2 team members to try out the form. They opened it from their machines, Logged By field defaulted to their names, filled in the rest of the fields and saved the record. Then when I opened the form from my machine and opened the underlying table, the records they created had my name instead of theirs. I think it overwrites it by user that is logged in. Any idea why that happens?

I have a very strong hunch you are setting the Me!LoggedBy field whenever the form is handled. You need to protect the original entry. The assignment from the Team Member table should only happen once, when you have a new record.

Ie,

If Me.NewRecord Then 
    Me!LoggedBy = .....
End If

Go through your code and make sure that Me!LoogedBy is not assigned a value anywhere else.

Best,
Jiri

 

[vspsdca]

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:

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 we send out to the business when their request is done. First when the form is opened, 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 create a new number unless one of the fields is filled in. Now since the 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 creating a reference number until the record is saved.

 

[Solo712]

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:

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 we send out to the business when their request is done. First when the form is opened, 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 create a new number unless one of the fields is filled in. Now since the 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 creating a reference number until the record is saved.

Hi. AFAIK you can't restrict the generation of the autonumber but you can move the code for the LoggedBy, which causes the form to get dirty and generates the autonumber, to the form's BeforeUpdate event.

Best,
Jiri