Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2012
    Posts
    126

    Unanswered: VBA user authentication

    I have a table called tblUserID with fields UserId and Password. I have a form called frmLogin that have text boxes called UserID and Password. I put a cmd button called cmdEnter and need it to validate the userid and password in the text boxes against those in the table. This is my best guess on the code

    Private Sub cmdEnter_Click()
    On Error GoTo Err_cmdEnter_Click

    If Me.userID = tblUserID.UserID and me.password = tblUserID.Password Then
    DoCmd.Openform “frmDashboard”
    Else
    MsgBox ("The username and or password you entered is invalid”)
    Me.userID.SetFocus
    End If


    Exit_cmdEnter_Click:
    Exit Sub
    Err_cmdEnter_Click:
    MsgBox Err.Description
    Resume Exit_cmdEnter_Click
    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    this is my best guess isn't really a question
    what is your problem
    what have you donme to try and resolve the issue?

    what do you think
    Code:
    If Me.userID = tblUserID.UserID and me.password = tblUserID.Password Then
    should do

    in this case If I were you I'd use a domaon function such as dlookup to verify the userid and password is valid*

    eg
    Code:
    if dlookup("userid", "mytable", "Userid = " & chr(39) & me.password & chr39 & " AND Password = " & chr(39) & me.password & chr(39) = NULL then ' we didn't find that useridf and password combination
    'insert error processign code here
    else
    'insert on success code here
    endif

    *well truth to be told I wouldn't be rolling my own security code I'd use the network logon and then find out if that person is authenticated
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2012
    Posts
    126
    Quote Originally Posted by healdem View Post
    this is my best guess isn't really a question
    what is your problem
    what have you donme to try and resolve the issue?

    what do you think
    Code:
    If Me.userID = tblUserID.UserID and me.password = tblUserID.Password Then
    should do

    in this case If I were you I'd use a domaon function such as dlookup to verify the userid and password is valid*

    eg
    Code:
    if dlookup("userid", "mytable", "Userid = " & chr(39) & me.password & chr39 & " AND Password = " & chr(39) & me.password & chr(39) = NULL then ' we didn't find that useridf and password combination
    'insert error processign code here
    else
    'insert on success code here
    endif

    *well truth to be told I wouldn't be rolling my own security code I'd use the network logon and then find out if that person is authenticated
    Sorry I think I worded this wrong. I've never done security in a db before and the code wasn't working but was my best effort. I want the if statement to verify what they entered in the text boxes equals what is in the table. If they do the dashboard opens and if they don't they try again.

    I see your point using Dlookup but am wondering what the chr(39) does?

    I'd love to validate this against there network credentials but theres two issues. One they are not users on our network and two I have no idea how

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ....but am wondering what the chr(39) does?
    use the context sensitive help
    or if thats borked open up help in Access and search for the chr function
    or if that fails use google

    I'd love to validate this against there network credentials but theres two issues. One they are not users on our network and two I have no idea how
    for the network logon use the api calls.. google 'dev ashish api' or look through the code bank for some code form IIRC PBaldy.

    the network logon isn't foolproof, it can be incorrect in there is a snafu on the network when you first log on.
    your network policies have to be set such that only one comp;uter can be logged on by one user id

    what you can do is compare the network logon with your user table

    you should tie down who can make changes to the logon table such that a very very restricted set of trusted users can write or delete to that table, everyone else who should be able to read from the table

    you need a form to allow users to be added or deleted on the usertable

    the advantage of the api call is that your users no longer need to remember another password, password maintainence is controlled by your network trolls, as is user registration. assuming your network trolls do delete the id when someone leaves automatically the logon to your app also fails as they can no longer logon with that userid

    if you are paranoid you can use similar api calls to extract the computer id, and log the date and time. if thats done on a read only table then its nearly as good a legal replacement paper trail as you can get. (especially if you make that table write only).

    bear ion mind you can't prove who actually logs on the the network, beyond a specific user id + password combination, but if the sites policies reflect one user per logon and do not leave the computer logged on unattended then you should be OK.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2012
    Posts
    126
    Thank you Healdem!

    Im working with the code and this is it editted to my table

    Private Sub cmdEnter_Click()
    On Error GoTo Err_cmdenter_Click

    If DLookup("userid", "tblUserID", "Userid = " & Chr(39) & Me.password & chr39 & " AND Password = " & Chr(39) & Me.password & Chr(39) = Null) Then
    ' we didn't find that useridf and password combination
    'insert error processign code here
    MsgBox ("We did not find that userID and Password Combination")

    Me.UserID = ""
    Me.password = ""
    Me.UserID.SetFocus

    Else
    Me.Visible = False

    DoCmd.OpenForm "frmCartonLocal", acNormal, , , acFormAdd
    End If

    Exit_cmdclose_click:
    Exit Sub
    Err_cmdenter_Click:
    MsgBox Err.Description
    Exit Sub

    End Sub
    When i run it i get a type mismatch error highlighting the If statement. Any ideas?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what is the reported error message?
    first guess is a misplaced closing bracket
    instead of
    Code:
    Chr(39) = Null)
    try
    Code:
    Chr(39)) = Null
    dlookup is a function it returns a value....
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2012
    Posts
    126
    Quote Originally Posted by healdem View Post
    what is the reported error message?
    first guess is a misplaced closing bracket
    instead of
    Code:
    Chr(39) = Null)
    try
    Code:
    Chr(39)) = Null
    dlookup is a function it returns a value....

    Thank you. Tried that and now im getting the attached picture. Whats odd is I didn't enter temp1234 as my userid
    Attached Thumbnails Attached Thumbnails Untitled.jpg  

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK so you need to diagnose what is wrong with the posted code
    you need to understand what you are cut and pasting.
    bear in mind most code posted on this, and other similar forums, is untested and may contain errors
    it looks like the fragment of code I posted contained (at least) 3 errors, one of which has now been resolved
    there are at least two errors remaining.
    examine the code
    understand what each segment / function call is about.

    look at the image.. what is wrong
    then try and resolve what is causing that error
    then having identified the error try to work out what needs to be changed to make it work
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Aug 2012
    Posts
    126
    Yeah actually been trying to dissect it to understand it better. First time ever using dlookup.

    been looking on line to try to figure out exactly how this is working.

    The Expr which identifies field i want to return which in this case is UserID so this should be okay
    DLookup("userid",

    Identify the table or query I want to pull from which in this case is tbluserID so this should also be okay
    "tblUserID",

    This is where im having a little struggle
    "Userid = " & Chr(39) & Me.UserID & chr39 & "
    saying userid and ' = entered userID and '

    AND

    Password = " & Chr(39) & Me.password & Chr(39))

    which is saying password on table and ' equal password entered and '

    Do i need to call the password field on the tbl along with the userid field like i am?

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what does the function chr do?
    comparing the picture with what you think the code should generate?
    looks to me like thats 2 errors down one to go
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Aug 2012
    Posts
    126
    sounds like im making progress woohoo!

    From what I read chr(39) returns the ascii value ' my next question is why are we using it or i may be completely wrong.

    wait wouldn't the char39 or ' cancel the line of code? since putting a single quote in front of anything makes it information IE
    ' this how you open a query
    docmd.openquery "queryname"

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    functionally
    strSQL = "Select * from myTable where userid = '" & mycontrol & "'"
    is the same as
    strSQL = "Select * from myTable where userid = " & chr(39) & mycontrol & chr(39)
    the chr(39) returns the character represented by no 39 in the ascii table the ' symbol
    reasons for using it are primarily (human) legibility

    the problem is that chr39 is garbage.... it shoudl be chr(39)

    the clues
    well the error message in the image you supplied showed there was no closing quote...

    developing good debugging skills is not about asking questions of others, its about observing what the computer is telling you is wrong, trying to work out whether what you have coded is what you think you have coded, trying to fidn a fix for the fault, then proving that that fix is right
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Aug 2012
    Posts
    126
    crap. Alright the line of code works but its allowing me in whether or not i put in null values or wrong values.
    Last edited by Syrch; 08-16-13 at 18:28.

  14. #14
    Join Date
    Nov 2011
    Posts
    413
    If IsNull(DLookup("[UserID]", "tblUserID", "[UserID]='" & Me.UserID.Value & "' And [pw]='" & Me.password.Value & "'")) Then
    MsgBox "Your User ID and Password do not match." & vbCrLf & vbCrLf & _
    "Please try again.", vbCritical + vbOKOnly, "Oops!"
    Me.LIChk.Value = Me.LIChk.Value + 1
    If Me.LIChk.Value > 3 Then
    MsgBox "Your User ID and Password still do not match.", vbCritical + vbOKOnly, "No Hacking!"
    DoCmd.Quit
    End If
    Me.password.SetFocus
    Me.password.Value = Null


    Exit Sub
    End If

Posting Permissions

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