1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access help - password protected combo box

Discussion in 'Business Applications' started by reddfoot, Apr 13, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. reddfoot

    reddfoot Thread Starter

    Joined:
    Apr 13, 2010
    Messages:
    4
    Hi,

    I'm a rookie with Microsoft access, using 2003 right now, and need some help!

    I have a form with a combo box that selects from a table (User Table) which contains user names and passwords. I'm trying to make the combo box work so that when a user is selected, it prompts me to enter a password - it will then compare the password entered with the password found on the User Table for the specific user which was selected in the combo box.

    If the password entered = the password found on the User Table for that combo box selection, then it should simply allow that selection to be shown on the combo box. If the password entered is incorrect, then it should reset the combo box to be blank.

    This is basically a pseudo user signoff field. Each user should only be able to choose their username within this combobox, thus the password.

    I have no idea how to write this code. I can only assume that I would put the code in the After Update part of the combo box, and that I'd have to do some kind of Dlookup.

    Can anyone here help me with the code?

    thanks,
    Mike
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Mike, why don't you use the User Level Security?
     
  3. reddfoot

    reddfoot Thread Starter

    Joined:
    Apr 13, 2010
    Messages:
    4
    I'd rather not go through that process when all I need is just the code for this one field to work. This is really the last thing I have left before my database is complete. Is there a way to write this code?

    Here's what I tried, though I realize I'm still way off, and haven't added any code to blank out the combo box if the password entered is incorrect.. am i headed in the right direction at least? Like I said, I've never really written any code before.

    Private Sub Received_Signoff_AfterUpdate()
    Dim pwd As String
    pwd = InputBox("Please enter password", "Secure")
    If pwd = DLookup("Password", "User Table", pwd = "Password") Then
    MsgBox ("correct password")
    Else
    MsgBox ("Incorrect password")
    End If
    End Sub
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    That code looks pretty good, does it work?
    I think I can see one problem, the password may be in the Table, but not for the person selected.
    I would normally use a Recordset to check the Password based on the combo "Selection" rather than using the Dlookup. But if it works for you, use what you have.

    This is what the Recordset version looks like, you would obviously have to change the table & field names as this checks for a valid name and password.

    Dim rs As Object, rstable As Object, count As Integer, counter As Integer
    Dim sql As String, recount As Integer
    sql = "SELECT [tbl(Zn)UserNames].* " & _
    "FROM [tbl(Zn)UserNames] " & _
    "WHERE Username = '" & Me.UserLoginName & "' "
    Set rs = CurrentDb.OpenRecordset(sql)
    recount = rs.RecordCount

    If recount = 0 Then
    MsgBox "That is not a recognized Username. Username and" & Chr(13) & _
    "Password are case-sensitive. Try Again.", vbExclamation, "Invalid Username"
    Me.UserLoginName.SetFocus ' move curser back to Username field
    ' close the Recordset
    rs.Close
    Set rs = Nothing
    Exit Sub ' return control to User on the form to allow another try
    Else
    If Me.UserLoginPassword <> rs.Password Then
    ' Here if incorrect Password for this Username.
    MsgBox "That is not the correct Password for that Username" & Chr(13) & _
    "Passwords are case-sensitive. Try Again.", vbExclamation, "Invalid Password"
    Me.UserLoginPassword.SetFocus
    ' Start login process over again.
    ' Close the recordset.
    rs.Close
    Set rs = Nothing
    Exit Sub
    End If
    End If

    This code also conatined a count of user Attempts and if greater than 3 they were kicked out of the database, but I have removed that section, hopefully without breaking the rest of it. :)
     
  5. reddfoot

    reddfoot Thread Starter

    Joined:
    Apr 13, 2010
    Messages:
    4
    thanks for the feedback!

    My code didn't work, so I'm glad that you have proposed another option!! Couple things:

    1. I tried pasting your code into the after update part of the combo box, but it seems to be getting stuck here (Error msg says- Compile error: Member or Data Not found):

    Dim rs As Object, rstable As Object, count As Integer, counter As Integer
    Dim sql As String, recount As Integer
    sql = "SELECT [tbl(Zn)User_Table].* " & _
    "FROM [tbl(Zn)User_Table] " & _
    "WHERE User_Name = '" & Me.Received_Signoff & "' "
    Set rs = CurrentDb.OpenRecordset(sql)
    recount = rs.RecordCount
    UserLoginPassword = InputBox("Please enter password", "Secure")
    If recount = 0 Then
    MsgBox "That is not a recognized Username. Username and" & Chr(13) & _
    "Password are case-sensitive. Try Again.", vbExclamation, "Invalid Username"
    Me.Received_Signoff.SetFocus ' move curser back to Username field
    ' close the Recordset
    rs.Close
    Set rs = Nothing
    Exit Sub ' return control to User on the form to allow another try
    Else
    If Me.UserLoginPassword <> rs.Password Then
    ' Here if incorrect Password for this Username.
    MsgBox "That is not the correct Password for that Username" & Chr(13) & _
    "Passwords are case-sensitive. Try Again.", vbExclamation, "Invalid Password"
    Me.UserLoginPassword.SetFocus
    ' Start login process over again.
    ' Close the recordset.
    rs.Close
    Set rs = Nothing
    Exit Sub
    End If
    End If


    2. I didn't see any place where an input box pops up to enter a password, so I added this line:

    UserLoginPassword = InputBox("Please enter password", "Secure")

    However, when I tried it, no box popped up. Not sure why..

    3. I did my best to modify the table and field names, here's what I did.

    User_Table <-- is the name of the table to reference w/ user names and passwords
    User_Name <-- is the name of the field within User_Table that contains the username
    Received_Signoff <-- is the name of the combo box

    FYI : The User_Table table has 3 fields: User ID (primary key), User Name, Password.

    That being said, I'm not sure if I pasted the correct fields correctly at this part of the code:

    sql = "SELECT [tbl(Zn)User_Table].* " & _
    "FROM [tbl(Zn)User_Table] " & _
    "WHERE User_Name = '" & Me.Received_Signoff & "' "

    what does tbl(Zn) mean?




    hope I'm not confusing you!!
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    That tbl(Zn) is one problem, it is the prefix of the table of the guy I wrote it for so take that out.
    The other thing to do is check the VBA Library References, ensure that the Microsoft ActiveX Objects 2.1 Library it checked, your version may not be 2.1 though.
     
  7. reddfoot

    reddfoot Thread Starter

    Joined:
    Apr 13, 2010
    Messages:
    4
    thanks for your help!! With your code and some editing help from another person, it now works perfectly!!

    Here's the final code:

    Private Sub Received_Signoff_AfterUpdate()
    Dim pwd As String
    Dim rs As Object, rstable As Object, count As Integer, counter As Integer
    Dim sql As String, recount As Integer
    sql = "SELECT * " & _
    "FROM [User Table] " & _
    "WHERE [User ID] = " & Me.Received_Signoff
    Set rs = CurrentDb.OpenRecordset(sql)
    recount = rs.RecordCount
    'MsgBox "Record Count" & rs.Password
    pwd = InputBox("Please enter password", "Secure")
    If pwd = rs.Password Then
    rs.Close
    Set rs = Nothing
    Exit Sub ' return control to User on the form to allow another try
    Else
    If pwd <> rs.Password Then
    ' Here if incorrect Password for this Username.
    MsgBox "That is not the correct password for" & Chr(13) & _
    "that username. Please try Again.", vbExclamation, "Invalid Password"
    Me.Received_Signoff.SetFocus
    ' Start login process over again.
    ' Close the recordset.
    rs.Close
    Set rs = Nothing
    Exit Sub
    End If
    End If
    End Sub
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Well done.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/916617

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice