Solved

User login form not functioning properly

Posted on 2011-02-19
5
451 Views
Last Modified: 2012-05-11
Hi Experts,

I am attempting to create a user login form in access '07.  The code that I have so far is not erroring out, however, it is not doing what I want it to do.  It is a relatively simple login form.  The user opens the db and a login form is loaded.  The user will select their username from the combo box and then focus is placed on the password field.  The user enters in an assigned pwd given to them by the administrator.  When the user clicks on login, the code should compare the pwd string in the textbox to the adminassigned pwd on the users table.  The code is not doing that and it is not erroring out.  The 'Users' table has the following columns: username, password, assignedpassword and confirm password.  Can someone take a look at my code and tell me what I am missing?  Thanks!!
Option Compare Database
Const NumLoginAttempts = 3
Dim Attempts As Integer
Private Sub Cbousername_AfterUpdate()
'If a username is selected, then place the focus on the password field.
If Not IsNull(Me.Cbousername) Then
    Me.txtPwd.SetFocus
    Me.CmdLogin.Enabled = True
End If
    
End Sub
Private Sub CmdLogin_Click()

'Check to make sure that there are characters in the txtpwd field
'If not, display message telling user that a password must be entered and place focus back on the password box
If IsNull(Me.txtPwd) Then
    MsgBox "You must enter a password.", vbExclamation + vbOnly, "Required Entry"
    Me.txtPwd.SetFocus
End If
'Validate password for user
If Me.txtPwd.Value = DLookup("Password", "Users", "[ID]=" & Me.Cbousername.Value) Then
    ID = Me.Cbousername.Value
'Close the login form and open frmMain
    DoCmd.Close acForm, "FrmLogin", acSaveNo
    DoCmd.OpenForm "frmMain"
End If

If Me.txtPwd.Value <> DLookup("Password", "Users", "[ID]=" & Me.Cbousername.Value) Then
    MsgBox "The password you entered does not match the username selected.", vbExclamation + vbOnly, "Try Again"
    Me.txtPwd = ""
    Me.txtPwd.SetFocus
    Attempts = Attempts + 1
    'After 3 tries, the db will close
        If Attempts > 3 Then
            MsgBox "Maximum number of attempts reached. Please contact administrator to reset your password.", vbExclamation + vbOKOnly, "Database Shutdown"
            DoCmd.CloseDatabase
            Exit Sub
Else
'Compare txtpwd field value to the assignedpassword value for the user that is logging in
'If the password that the user types in matches the assignedpassword in tblusers, then have the user create a new password
If Me.txtPwd.Value = DLookup("AssignedPassword", "Users", "[ID]=" & Me.Cbousername & " Or [Password] Is null") Then
    MsgBox "You must enter a new password.", vbExclamation + vbOKOnly, "Invalid Password"
    Me.LblPwd.Visible = False
    Me.txtPwd.Visible = False
    Me.LblNewPwd.Visible = True
    Me.TxtNewPwd.Visible = True
    Me.TxtNewPwd.SetFocus

End If
End If
End If
End Sub

Open in new window

0
Comment
Question by:daintysally
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 21
ID: 34935999
Try:

Option Compare Database
Const NumLoginAttempts = 3
Dim Attempts As Integer
Private Sub Cbousername_AfterUpdate()
'If a username is selected, then place the focus on the password field.
If Not IsNull(Me.Cbousername) Then
    Me.txtPwd.SetFocus
    Me.CmdLogin.Enabled = True
End If
    
End Sub
Private Sub CmdLogin_Click()

'Check to make sure that there are characters in the txtpwd field
'If not, display message telling user that a password must be entered and place focus back on the password box
If IsNull(Me.txtPwd) Then
    MsgBox "You must enter a password.", vbExclamation + vbOnly, "Required Entry"
    Me.txtPwd.SetFocus
End If
'Validate password for user
If Me.txtPwd = DLookup("Password", "Users", "[ID]=""" & Me.Cbousername & """") Then
    ID = Me.Cbousername
'Close the login form and open frmMain
    DoCmd.Close acForm, "FrmLogin", acSaveNo
    DoCmd.OpenForm "frmMain"
End If

If Me.txtPwd <> DLookup("Password", "Users", "[ID]=""" & Me.Cbousername & """") Then
    MsgBox "The password you entered does not match the username selected.", vbExclamation + vbOnly, "Try Again"
    Me.txtPwd = ""
    Me.txtPwd.SetFocus
    Attempts = Attempts + 1
    'After 3 tries, the db will close
        If Attempts > 3 Then
            MsgBox "Maximum number of attempts reached. Please contact administrator to reset your password.", vbExclamation + vbOKOnly, "Database Shutdown"
            DoCmd.CloseDatabase
            Exit Sub
Else
'Compare txtpwd field value to the assignedpassword value for the user that is logging in
'If the password that the user types in matches the assignedpassword in tblusers, then have the user create a new password
If Me.txtPwd = DLookup("AssignedPassword", "Users", "[ID]=""" & Me.Cbousername & """) Or DLookup("Password", "Users", "[ID]=""" & Me.Cbousername & """") = "" Then
    MsgBox "You must enter a new password.", vbExclamation + vbOKOnly, "Invalid Password"
    Me.LblPwd.Visible = False
    Me.txtPwd.Visible = False
    Me.LblNewPwd.Visible = True
    Me.TxtNewPwd.Visible = True
    Me.TxtNewPwd.SetFocus

End If
End If
End If
End Sub

Open in new window

0
 
LVL 6

Expert Comment

by:Gugro
ID: 34936877
> "The code is not doing that"
What exactly is the code not doing?
- will the sub cmdLogin_Click not get called ?
- will the code not ask you for a password, when you entered none ?
- will the code not open "frmMain", when you gave the correct password ?
- will the code not warn you if you gave a wrong password ?

The best way to find the culprit: set a breakpoint on the start of Sub  cmdLogin_Click and step through your code ;-)

PS:
I would guess that "frmMain" will not open,  as you  are closing this form first, so it will never reach the second line.
    DoCmd.Close acForm, "FrmLogin", acSaveNo
    DoCmd.OpenForm "frmMain"
0
 

Author Comment

by:daintysally
ID: 34937480
the code does not work as it should at this point:

'Compare txtpwd field value to the assignedpassword value for the user that is logging in
'If the password that the user types in matches the assignedpassword in tblusers, then have the user create a new password
If Me.txtPwd = DLookup("AssignedPassword", "Users", "[ID]=""" & Me.Cbousername & """) Or DLookup("Password", "Users", "[ID]=""" & Me.Cbousername & """") = "" Then
    MsgBox "You must enter a new password.", vbExclamation + vbOKOnly, "Invalid Password"
    Me.LblPwd.Visible = False
    Me.txtPwd.Visible = False
    Me.LblNewPwd.Visible = True
    Me.TxtNewPwd.Visible = True
    Me.TxtNewPwd.SetFocus
0
 

Author Comment

by:daintysally
ID: 34937493
when I enter in the password that is equivalent to the assigned password, the code should prompt me to enter a new password and it does not.  Everything else works as it should.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 35023376
Looking at the later part of your code, you have an OR statement in there.
You also have a lot of nested ifs and double quotes.
All of those are a bugger to debug.
Dlookup isn't the world's happiest thing either.

First, does you users table have a numeric UserID as a primary key?
If it does, and it should, get your combo box to return 2 columns, UserID and Users
Format the combo box column widths for 0, 1"--or whatever width the single column has now.
Now you've done away with all the nasty doublequotes
I use Chr(34) & SomeStringVariable & Chr(34) instead of doublequotes when I do have to use them, becuase they are so ugly to debug.

Now, the logic
Option Compare Database
Option Explicit
Const NumLoginAttempts = 3
Dim Attempts As Integer
Private Sub Cbousername_AfterUpdate()
'If a username is selected, then place the focus on the password field.
If Not IsNull(Me.Cbousername) Then
    Me.txtPwd.SetFocus
    Me.CmdLogin.Enabled = True
End If
    
End Sub

Private Sub CmdLogin_Click()
dim db as database
dim rs as recordset

'Check to make sure that there are characters in the txtpwd field
'If not, display message telling user that a password must be entered and place focus back on the password box
If IsNull(Me.txtPwd) Then
    MsgBox "You must enter a password.", vbExclamation + vbOnly, "Required Entry"
    Me.txtPwd.SetFocus
End If

'Validate password for user
set db = currebtdb
set rs = db.openrecordset("select * from Users where UserID = " & me.Cbousername & ";", dbopendynaset, dbseechanges)

'this works REALLY well to replace a lot of nested ifs
'only the first true statement gets processed
Select case true
    case Me.txtPwd = rs!Password
	'I assume ID is some global variable not listed in your code
        ID = Me.Cbousername
        'Close the login form and open frmMain
         DoCmd.Close acForm, "FrmLogin", acSaveNo
         DoCmd.OpenForm "frmMain"
    case Me.txtPwd <> rs!Password and Me.txtPwd <> rs!AssignedPassword
        MsgBox "The password you entered does not match the username selected.", vbExclamation + vbOnly, "Try Again"
        Me.txtPwd = ""
        Me.txtPwd.SetFocus
        Attempts = Attempts + 1
        'After 3 tries, the db will close
        If Attempts > 3 Then
            MsgBox "Maximum number of attempts reached. Please contact administrator to reset your password.", vbExclamation + vbOKOnly, "Database Shutdown"
            DoCmd.CloseDatabase ' bail the db entirely
            Exit Sub
        else
            exit sub 'just bail the sub on attempts 1 and 2
	end if
    case Me.txtPwd <> rs!Password and Me.txtPwd = rs!AssignedPassword
    'if the password doesn't match, but the assigned password does, then have the user create a new password
        MsgBox "You must enter a new password.", vbExclamation + vbOKOnly, "Invalid Password"
        Me.LblPwd.Visible = False
        Me.txtPwd.Visible = False
        Me.LblNewPwd.Visible = True
        Me.TxtNewPwd.Visible = True
        Me.TxtNewPwd.SetFocus
end select

rs.close
db.close

End Sub

Open in new window

0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question