Results 1 to 14 of 14
  1. #1
    donsi is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    16

    Entering auto username in the form for data entry

    Hi everyone,

    I am a newbie to access and VAB but with the professor "Google's' help I am able to learn, and successfully built part of the database. I have login screen created and all the forms functions as intended, however I am struggling with duplicate records. I have a tblCustomers and tblEmployees, 3 tables for tbllog1,2,3 and three forms where user will enters data. When user enters data, forms auto fills the UserID field based on current user logged in, but upon clicking submit, it created duplicate userid in tblemployees. I try to figured it out myself but no luck so far. Please help.

    Login screen code:
    Code:
    Option Compare DatabaseOption Explicit
    
    
    Private Sub BtnExit_Click()
    Application.Quit
    End Sub
    
    
    Private Sub BtnLogin_Click()
        Dim rs As Recordset
       
    
    
    'Check to see if data is entered into Username
     
        If IsNull(Me.TxtUserID) Then
            MsgBox "Please enter a UserID.", vbOKOnly, "Required Data"
                Me.TxtUserID.SetFocus
            Exit Sub
        End If
    
    
    'Check to see if data is entered into Password
    
    
        If IsNull(Me.TxtPassword) Then
            MsgBox "You must provide a password.", vbOKOnly, "Required Data"
                Me.TxtPassword.SetFocus
            Exit Sub
        End If
    
    
    'Lookup correct password for username entered in Username
    
    
        Set rs = CurrentDb.OpenRecordset("tblEmployee", dbOpenSnapshot, dbReadOnly)
        rs.FindFirst "UserId='" & Me.TxtUserID & "'"
        
        If rs.NoMatch = True Then
            MsgBox "Incorrect User Name."
              Me.TxtUserID.SetFocus
            Exit Sub
            TempVars.Add "User", Me.TxtUserID
        End If
        
        If rs!Password <> Nz(Me.TxtPassword, "") Then
            MsgBox "Incorrect Password."
              Me.TxtPassword.SetFocus
            Exit Sub
        End If
        Me.Visible = False
        DoCmd.OpenForm "FrmMainMenu"
        
        
    
    
    End Sub
    Auto userid filled code


    Code:
    Private Sub Text21_GotFocus()Me.Text21.Value = Forms!Login!TxtUserID
    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sounds like the user textbox is bound to the field in the employee table rather than the field in the transaction table.

    Why 3 by the way?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    donsi is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    16
    Quote Originally Posted by pbaldy View Post
    Sounds like the user textbox is bound to the field in the employee table rather than the field in the transaction table.

    Why 3 by the way?
    There are three different type of transactions which user requested to have three forms. Form is build on the query of TblLog1, tblcustomers and tblemployee. field is bound to UserId field in query. Should i have useID field in TblLog1?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I wouldn't have 3 tables unless the fields were really different. Typically a "TransactionType" field in a single table. If you want to store the user who added the record, yes there needs to be a field in the transaction table.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    donsi is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    16
    So I put the UserID in transaction table and coded below to return UserID value, but getting an error
    Code:
    Private Sub Text21_GotFocus()
    Dim varU As Integer
    varU = DLookup("ID", "TblEmployee", "UserID = " & [Forms]![Login]![TxtUserID])
    Me.Text21 = varU
    End Sub
    Click image for larger version. 

Name:	Capture.PNG 
Views:	19 
Size:	21.5 KB 
ID:	25862

    AJ6154 is the username used to login to the database.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Clearly your value is text, so the variable has to be declared as a String and the DLookup() needs delimiters:

    http://access.mvps.org/access/general/gen0018.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    donsi is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    16
    Honestly, I am very confuse with this. So TblEmployee contains ID(Auto Number) field, UserID (User Name) and Password. When user logs in with UserID, Login form is hidden on background. Now I would like to take this UserID(username) from the Login screen and get a matching ID # from TblEmployee and assign it to EmpID (foreign key) in TblGroupLog. How do I achieve this? or is there a better way for me to get logged in user's name in TblGroupLog. Just trying to get user tied to record.
    Thank you

  8. #8
    donsi is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    16
    Tried as string and Variant, both ended up in same error.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you change the DLookup()? If the login form is still open, why not just:

    Me.Text21 = Forms!LoginFormName.TextboxName
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    donsi is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    16
    That was my initial code, but it creates duplicate entries in my TblEmployees.

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Because the textbox was bound to a field in the employee table instead of the transaction table; like I said in my first response.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    donsi is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    16
    I had it setup as you explained previously. I know there must be a simple solution and I may be explaining incorrectly. So let's try this
    • Employee Table: Three field


    1. ID- Autonumber (set as number and Primary Key)
    2. UserID- (AKA Username) (set as short text)
    3. Password- (set as short text)


    • Group Log Table:


    1. EmpID (not userID) field as a part of it along with all other fields for the log.


    • Customer Table:


    1. Cust FN
    2. Cust LN
    3. Cust Acc (Primary Key)


    • Query Group Log setup combining Cust, Emp and Group Log table.
    • Login form


    1. asks user to enter UserID (#2- Username) in txtUserId and password (#3)
    2. form hides after successfull login.


    • Group Log form (based on Query)
      1. User enters data
      2. UserId (Text21) box should populate on Setfocus from Login Form- txtUserID.
      3. Code used for value is
        Code:
         Me.Text21.Value = Forms!Login!TxtUserID
      4. Now here is the problem. If I tie UserID field here, it will create duplicate record (UserID is not a primary key)

    • Problem:
      • I want to get a value of EmpID field return to Text21 but display UserID.

  13. #13
    donsi is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    16
    I just solved it. Thanks and apology Paul.

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Ah good, no problem. I would have the form bound to the log table alone, not a query.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Auto Entering Form with a button
    By vicrob6 in forum Forms
    Replies: 3
    Last Post: 09-05-2015, 10:38 AM
  2. Replies: 3
    Last Post: 02-25-2015, 04:48 PM
  3. Replies: 3
    Last Post: 04-23-2014, 10:08 PM
  4. Replies: 5
    Last Post: 02-05-2013, 10:57 PM
  5. Automatic Username On Data Entry
    By netchie in forum Programming
    Replies: 10
    Last Post: 02-17-2012, 03:31 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