Results 1 to 8 of 8
  1. #1
    Summit_IT is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Location
    Virginia
    Posts
    47

    Question How do I autopopulate current users username into a form based on login information

    I have a login form that I it to autopopulate a table record with the usersID after he logs in. I need this to keep track of their time.

    Click image for larger version. 

Name:	Capture of login.JPG 
Views:	25 
Size:	19.1 KB 
ID:	46955

    Click image for larger version. 

Name:	Capture of Table.JPG 
Views:	25 
Size:	14.7 KB 
ID:	46954
    The table has two text formatted records. Now whenever you want to get the current users username, I can use this:

    Dlookup("Val","tblLocalVars", "Key='LoggedUser'") to populate a textbox in a form.




    [Option Compare DatabasePrivate intLogonAttempts As Integer


    Private Sub Form_Open(Cancel As Integer)
    'On open set focus to combo box
    Me.cboEmployee.SetFocus
    End Sub


    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 tblUsers to see if this matches value chosen in combo box


    If Me.txtPassword.Value = DLookup("strEmpPassword", "tblUsers", "[lngEmpID]=" & Me.cboEmployee.Value) Then


    lngMyEmpID = Me.cboEmployee.Value


    'This is what I am stuck on. I want to update a table record when user logs on. Then delete the record when he logs off. I want a Time Sheet form to use Lookup to autopopulate the User into the Time Sheet. The Code in blue is not working. The tblLocalVars is a temporary table and is located in the database frontend.

    CurrentDb.Execute "UPDATE tblLocalVars SET Val='" & Me.lngEmpID & "' WHERE Key='LoggedUser'"
    Me.Visible = False




    'Close logon form and open splash screen

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


    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 your system administrator.", vbCritical, "Restricted Access!"
    Application.Quit
    End If

    End Sub




    Private Sub txtPassword_AfterUpdate()
    Me.cmdLogin.SetFocus
    End Sub
    ]

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    First, do not use Val or Key for object names - they are both reserved words and can cause issues if you do so.
    http://www.allenbrowne.com/AppIssueBadWord.html

    second, please post your code within code tags (# on posting toolbar) and use proper indentation in your code. You should be able to go back and edit your post rather than repost the code.

    EDIT - forgot to write that IMO you should have a table for logged on users and append their data to that table and delete when they log out. You'd use the primary key value from your user table, not their name, plus anything else you deem pertinent (e.g. pc they're using).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You seem to store the employee ID in the tblLocalVars which is probably a number (long), so maybe try:
    Code:
    CurrentDb.Execute "UPDATE tblLocalVars SET Val=" & Me.lngEmpID & " WHERE Key='LoggedUser'"
    Next time please try to post code using the code tags (# in the forum).
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You seem to store the employee ID in the tblLocalVars which is probably a number (long), so maybe try:
    Code:
    CurrentDb.Execute "UPDATE tblLocalVars SET Val=" & Me.lngEmpID & " WHERE Key='LoggedUser'"
    Next time please try to post code using the code tags (# in the forum).
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    Summit_IT is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Location
    Virginia
    Posts
    47
    Code:
    Option Compare DatabasePrivate intLogonAttempts As Integer
    
    
    Private Sub Form_Open(Cancel As Integer)
    'On open set focus to combo box
    Me.cboEmployee.SetFocus
    End Sub
    
    
    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 tblUsers to see if this matches value chosen in combo box
    
    
    If Me.txtPassword.Value = DLookup("strEmpPassword", "tblUsers", "[lngEmpID]=" & Me.cboEmployee.Value) Then
    
    
    lngMyEmpID = Me.cboEmployee.Value
    
    
    'This is what I am stuck on. I want to update a table record when user logs on. Then delete the record when he logs off. I want a Time Sheet form to use Lookup to autopopulate the User into the Time Sheet. The Code in blue is not working. The tblLocalVars is a temporary table and is located in the database frontend.
    
    CurrentDb.Execute "UPDATE tblLocalVars SET Val='" & Me.lngEmpID & "' WHERE Key='LoggedUser'"
    Me.Visible = False
    
    
    
    
    'Close logon form and open splash screen
    
    DoCmd.Close acForm, "frmLogon", acSaveNo
    DoCmd.OpenForm "frmSplash_Screen"
    
    
    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 your system administrator.", vbCritical, "Restricted Access!"
    Application.Quit
    End If
    
    End Sub
    
    
    
    
    Private Sub txtPassword_AfterUpdate()
    Me.cmdLogin.SetFocus
    End Sub

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Did you try my suggestion from post #4?
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    Summit_IT is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Location
    Virginia
    Posts
    47
    I figured it out. Thanks gentlemen. Much appreciated.

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

Similar Threads

  1. Access - Login Form and Store ID/Username
    By RiskIt in forum Access
    Replies: 3
    Last Post: 01-07-2019, 03:08 AM
  2. Replies: 1
    Last Post: 02-17-2013, 04:46 PM
  3. Username and passwod login using Form
    By Tom1 in forum Programming
    Replies: 4
    Last Post: 07-04-2012, 12:29 PM
  4. Replies: 10
    Last Post: 03-02-2012, 11:06 AM
  5. Replies: 1
    Last Post: 01-04-2008, 11:40 AM

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