By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 1,207 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

Select statement

P: 10
Ok I am an old time programmer but I can not understand why I get a compile error on the following statement. I am using access 2003

SELECT tblUser.User FROM tblUser

I have a form where I want the user to type an ID and Password then upon exiting the password I want to check a table to be sure the combination is there and also assign a level of security based upon a third field in the table. The table contains:
User
Password
Level

Thanks
Apr 2 '10 #1

✓ answered by ADezii

  1. Assumptions:
    1. Table Name is tblLogins, and consists of Fields [UserID] {TEXT} and [Password] {TEXT}
    2. Field Name on Form to accept UserID is txtUserID
    3. Field Name on Form to accept Password is txtPassword
    4. Case Sensitive comparison on Password, namely: Password <> PassworD
  2. Code definition:
    Expand|Select|Wrap|Line Numbers
    1. Dim strPassWord As String
    2. Dim txtUsr As TextBox
    3. Dim txtPWrd As TextBox
    4.  
    5. Set txtUsr = Me![txtUserID]
    6. Set txtPWrd = Me![txtPassword]
    7.  
    8. 'Make sure there is something entered in each Text Box
    9. If IsNull(txtUsr) Or IsNull(txtPWrd) Then Exit Sub
    10.  
    11. 'Mask the Password
    12. txtPWrd.InputMask = "Password"
    13.  
    14. 'First, check for a Valid UserID, the one entered by the User
    15. If DLookup("[UserID]", "tblLogins", "[UserID] = '" & txtUsr & "'") <> "" Then
    16.   strPassWord = DLookup("[Password]", "tblLogins", "[UserID] = '" & txtUsr & "'")
    17.     'UserID OK, but must now perform a 'Case-Sensitive' comparison on the Password
    18.     If StrComp(strPassWord, txtPWrd, vbBinaryCompare) = 0 Then
    19.       'ALL is OK, User ID and Password match exactly, proceed
    20.       MsgBox "Valid UserID/Password combination!"
    21.     Else
    22.       MsgBox txtPWrd & " is not a Valid Password", vbExclamation, "Password Not Found"
    23.     End If
    24. Else
    25.   MsgBox txtUsr & " is an Invalid User ID", vbExclamation, "User ID Not Found"
    26. End If

Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,638
This is simply a SELECT Statement that will return all Values in the [User] Field in tblUser, nothing more.
Apr 2 '10 #2

P: 10
This I know. Why will it not compile and how would I code a lookup to check a user name and password. To my old timer way of thinking you would have the user key in an id and password then look up that user's record in a table and check to see if the typed password matches the one in the table. You would then be able to use other info in the table to determine what access to give this user.
Thanks
Apr 3 '10 #3

ADezii
Expert 5K+
P: 8,638
  1. Assumptions:
    1. Table Name is tblLogins, and consists of Fields [UserID] {TEXT} and [Password] {TEXT}
    2. Field Name on Form to accept UserID is txtUserID
    3. Field Name on Form to accept Password is txtPassword
    4. Case Sensitive comparison on Password, namely: Password <> PassworD
  2. Code definition:
    Expand|Select|Wrap|Line Numbers
    1. Dim strPassWord As String
    2. Dim txtUsr As TextBox
    3. Dim txtPWrd As TextBox
    4.  
    5. Set txtUsr = Me![txtUserID]
    6. Set txtPWrd = Me![txtPassword]
    7.  
    8. 'Make sure there is something entered in each Text Box
    9. If IsNull(txtUsr) Or IsNull(txtPWrd) Then Exit Sub
    10.  
    11. 'Mask the Password
    12. txtPWrd.InputMask = "Password"
    13.  
    14. 'First, check for a Valid UserID, the one entered by the User
    15. If DLookup("[UserID]", "tblLogins", "[UserID] = '" & txtUsr & "'") <> "" Then
    16.   strPassWord = DLookup("[Password]", "tblLogins", "[UserID] = '" & txtUsr & "'")
    17.     'UserID OK, but must now perform a 'Case-Sensitive' comparison on the Password
    18.     If StrComp(strPassWord, txtPWrd, vbBinaryCompare) = 0 Then
    19.       'ALL is OK, User ID and Password match exactly, proceed
    20.       MsgBox "Valid UserID/Password combination!"
    21.     Else
    22.       MsgBox txtPWrd & " is not a Valid Password", vbExclamation, "Password Not Found"
    23.     End If
    24. Else
    25.   MsgBox txtUsr & " is an Invalid User ID", vbExclamation, "User ID Not Found"
    26. End If
Apr 3 '10 #4

P: 10
Thanks very much. It is hard for me to wrap myself around some of this code when I try it myself. I understand what you sent tho. I have been programming since 1965 using basic, NCR Neat/3 and a db language that you have probably never heard of TAS. Some of the pictures are coming into focus thanks to people like you!
Apr 3 '10 #5

ADezii
Expert 5K+
P: 8,638
@erniemack
Glad to be of service to you. Programming is like ridding a bicycle - once you get back on, it's like you never forgot! (LOL)
Apr 3 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.