Results 1 to 4 of 4
  1. #1
    psunilve is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2013
    Posts
    4

    Retrieve the currentuser from Users table (Acc2007)

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

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    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") & "'")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    psunilve is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2013
    Posts
    4
    Quote Originally Posted by June7 View Post
    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.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    No, that is the nature of autonumber field. Data entry in other fields initiates record and ID is generated.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Copy From Command Button (Acc2007 form)
    By psunilve in forum Programming
    Replies: 4
    Last Post: 04-03-2013, 06:11 AM
  2. retrieve table contents from an array
    By bonecone in forum Programming
    Replies: 1
    Last Post: 05-22-2012, 12:51 PM
  3. How to retrieve field names in a table
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 12-05-2010, 09:09 PM
  4. CurrentUser()
    By OpsO in forum Access
    Replies: 8
    Last Post: 09-16-2010, 11:37 AM
  5. CurrentUser dependent data in formular
    By Patience in forum Access
    Replies: 1
    Last Post: 05-11-2010, 12:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums