Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    61

    Unanswered: Auto Enter User Name on a form

    Hi There,

    I have created an access 2007 DB for cycle countinh. I have created a form where the user must enter the part #, the quantity, the serial number, and the counters name.

    I have found that the best way to set up any of these forms is to have as many as possible of the fields auto populate. What I would like to do is have the user name auto populate according to who is doing the entry. I was hoping that when the user opened the form, it would ask for their name, which would then populate the user name field automatically for all of the records that they entered.

    Is ther anyway that this can done?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Create a Module and add this code into it:
    Code:
    Private m_strUserName As String
    
    Public Function SetUserName()
    
        Dim strUserName As String
        
        strUserName = InputBox("Please enter your user name:")
        If Len(strUserName) > 0 Then m_strUserName = strUserName
        
    End Function
    
    Public Function GetUserName() As String
    
        GetUserName = m_strUserName
        
    End Function
    2. Open the Module of the form and add this code to it:
    Code:
    Private Sub Form_Current()
    
        If Len(Nz(Me.Text_UserName)) = 0 Then Me.Text_UserName = GetUserName
        
    End Sub
    
    Private Sub Form_Load()
    
        SetUserName
        
    End Sub
    If you want to use the same principle for several forms you can call the function SetUserName when your application starts instead of calling it when the form loads.

    Its also possible to retrieve the Windows user name, change the code in the module like this:
    Code:
    Public Declare Function GetNTUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
    
    Private m_strUserName As String
    
    Public Function SetUserName()
    
    '    Dim strUserName As String
    '
    '    strUserName = InputBox("Please enter your user name:")
    '    If Len(strUserName) > 0 Then m_strUserName = strUserName
        m_strUserName = GetNTUser
        
    End Function
    
    Public Function GetUserName() As String
    
        GetUserName = m_strUserName
        
    End Function
    
    Public Function GetNTUser() As String
    '
    ' Retrieve the ID of the currently connected user
    '
        Dim strUserName As String
        Dim lngUserNameSize As Long
        
        strUserName = String$(255, 0)
        lngUserNameSize = Len(strUserName)
        GetNTUserName strUserName, lngUserNameSize
        strUserName = Left$(strUserName, lngUserNameSize - 1)
        GetNTUser = strUserName
        
    End Function
    You can also call GetNTUser directly from a form but this means that an API call will be performed each time you will refernece it.
    Have a nice day!

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The simplest way would be to use the AfterUpdate event of the User Name Control to set the Control's Default Value. The user enters his/her name on the first record they enter, and from that time forward, until the name is either manually changed or the form is closed, the data will be entered automatically in each new record.
    Code:
    Private Sub UserName_AfterUpdate()
       Me.UserName.DefaultValue = """" &    Me.UserName.Value & """"
    End Sub
    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Aug 2009
    Posts
    61
    Hi There,

    This code works really well...Thanks!

    I also have some other code running in order to have the user enter a password before being allowed to access the DB....curtosy of Allen Browne. How would I integrate the two sets of code so that when the user logs in, it fills there username into each form that has a username field? I have pasted the code that i have below.

    Private Sub cboEmployee_AfterUpdate()
    'After selecting user name set focus to password field
    Me.txtPassword.SetFocus
    End Sub


    Private Sub cmdLogin_Click()

    'Check to see if data is entered into the UserName combo box

    If IsNull(Me.CboEmployee) Or Me.CboEmployee = "" Then
    MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
    Me.CboEmployee.SetFocus
    Exit Sub
    End If

    'Check to see if data is entered into the password box

    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
    MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
    Me.txtPassword.SetFocus
    Exit Sub
    End If

    'Check value of password in tblEmployees to see if this
    'matches value chosen in combo box

    If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", _
    "[lngEmpID]=" & Me.CboEmployee.Value) Then

    lngMyEmpID = Me.CboEmployee.Value

    'Close logon form and open splash screen

    DoCmd.Close acForm, "frmLogon", acSaveNo
    DoCmd.OpenForm "switchboard"

    Else
    MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
    "Invalid Entry!"
    Me.txtPassword.SetFocus
    End If

    'If User Enters incorrect password 3 times database will shutdown

    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
    MsgBox "You do not have access to this database.Please contact admin.", _
    vbCritical, "Restricted Access!"
    Application.Quit
    End If

    End Sub

  5. #5
    Join Date
    Aug 2009
    Posts
    61
    Quote Originally Posted by Sinndho View Post
    1. Create a Module and add this code into it:
    Code:
    Private m_strUserName As String
    
    Public Function SetUserName()
    
        Dim strUserName As String
        
        strUserName = InputBox("Please enter your user name:")
        If Len(strUserName) > 0 Then m_strUserName = strUserName
        
    End Function
    
    Public Function GetUserName() As String
    
        GetUserName = m_strUserName
        
    End Function
    2. Open the Module of the form and add this code to it:
    Code:
    Private Sub Form_Current()
    
        If Len(Nz(Me.Text_UserName)) = 0 Then Me.Text_UserName = GetUserName
        
    End Sub
    
    Private Sub Form_Load()
    
        SetUserName
        
    End Sub
    If you want to use the same principle for several forms you can call the function SetUserName when your application starts instead of calling it when the form loads.

    Its also possible to retrieve the Windows user name, change the code in the module like this:
    Code:
    Public Declare Function GetNTUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
    
    Private m_strUserName As String
    
    Public Function SetUserName()
    
    '    Dim strUserName As String
    '
    '    strUserName = InputBox("Please enter your user name:")
    '    If Len(strUserName) > 0 Then m_strUserName = strUserName
        m_strUserName = GetNTUser
        
    End Function
    
    Public Function GetUserName() As String
    
        GetUserName = m_strUserName
        
    End Function
    
    Public Function GetNTUser() As String
    '
    ' Retrieve the ID of the currently connected user
    '
        Dim strUserName As String
        Dim lngUserNameSize As Long
        
        strUserName = String$(255, 0)
        lngUserNameSize = Len(strUserName)
        GetNTUserName strUserName, lngUserNameSize
        strUserName = Left$(strUserName, lngUserNameSize - 1)
        GetNTUser = strUserName
        
    End Function
    You can also call GetNTUser directly from a form but this means that an API call will be performed each time you will refernece it.
    Hi Sinndho,

    How would I put the second string of code in my DB so that it runs when the application starts? Should it be attached to the switchboard somehow?

    Thanks

    Martin

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can paste that code into a module then call SetUserName() in the Form_Open or Form_Load event handler of the Switchboard form (if this one starts when whe database is open) or call the function through an AutoExec macro.

    Later on you can call the GetUserName() function (if you used SetUserName()) or directly call GetNTUser() (if you did not) every time you need to retrieve the name of the user:
    Code:
    Me.Text_UserID.Value = GetUserName
    or:
    Code:
    Me.Text_UserID.Value = GetNTUser
    Be aware that when using SetUserName(), the user will be able to type anything he or she wants, while using GetNTUser() the id of the user that opened the Windows session will be used (GetNTUser() retrieves the information from the Windows credentials locally or in Active Directory and does not asks for a user id).
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •