Login Form - Check if password reset flag before continuing.

[inkbird01]

Hi, I have a login form and a table of users. In the table of users there is a column called TempPFlag that is set to '1' if the password has been reset.
I have the following code and want it to first check if the username and password entered matches what is in the table but also check if they need to reset their password - check if Tbl_users.TempPFlag = '1' then open form frm_reset

Private Sub cmd_login_Click()



    If IsNull(Me.txtloginID) Then

        MsgBox "Error", vbInformation, "Please enter a username"

        Me.txtloginID.SetFocus

    ElseIf IsNull(Me.txtPassword) Then

        MsgBox "Error", vbInformation, "Please enter a password"

        Me.txtPassword.SetFocus

    Else

 

        Credentials.UserName = Me.txtloginID.Value

        If DLookup("Password", "tbl_users", "UserName = '" & Credentials.UserName & "'") = Me.txtPassword Then

            Credentials.UserId = DLookup("ID", "tbl_users", "UserName = '" & Credentials.UserName & "'")

            Credentials.AccessLvlID = DLookup("AccessLvl", "tbl_users", "UserName = '" & Credentials.UserName & "'")

               

            Select Case Credentials.AccessLvlID

                Case 1

                    DoCmd.OpenForm "frm_1"

                Case 2

                    DoCmd.OpenForm "frm_2"

                Case 3

                    MsgBox "Your Account Has Been Deactivated. Please contact a super user."

                Case Else

                    DoCmd.OpenForm "frm_loginform"

            End Select

         

            If Me.txtPassword = "password" Then DoCmd.OpenForm "frm_userprofile" 'vlad moved the closing here to leave the user profile open

            DoCmd.Close acForm, Me.Name

        Else

            MsgBox "Incorrect Login or Password"

        End If

    End If

 

End Sub

 

[plog]

You never asked a question. I'm really confused because the new part you want to add isn't really that difficult to add and it's very similar to parts of your code already. And the way you described your code it seems you are more than capable of coding this new feature. So why are you posting this? What issues are you having?

With that said, a suggestion I have of your code is better structure. Don't mix your tests and your actions. Its easy to get lost and keep in your mind exactly the status of the submission and where in the process everything is happening. Especially when you have nested If and Case statements like that. My guess is your real question isn't how, but where do you add your new code feature.

I would separate your tests and your actions. Use a status variable to hold the results of all these tests you are doing then at the bottom perform a Case on that status variable and perform all the appropriate actions. That will eliminate most of the nesting you are doing and will help you structure your code so you can easily identify what part of your code is doing what. Here's some generic code to demonstrate:

int_Status = 0
' status variable 0 is  valid submission, will test for issues and set if found any

int_ID = 0
  ' will hold ID of user if submitted good username/password

if  isnull(Me.Password) then int_Status = 1
' no password submitted, error flagged

if isnull(Me.UserName) then int_Status = 2
  ' no user name submitted, error flagged

if int_Status= 0 then
' will see if username/password in  database,

  if this section fails then int_Status=3
 
  End if 


if int_Status = 0 then
  ' will get ID and TempPFlag values and 

  int_ID = Dlookup()
  if DLookup("TempPFlag") = 1  then int_Status = 4

  End If

' Testing above, below will be CAse statement to direct user to correct form/message

Select Case int_Status
       Case 0  ' valid login, no issues

       Case 1 'no password submitted
                   MSgBox("Please enter password")
    etc. etc.

Tests at the top, actions at the bottom, status variable to keep it all straight. You kind of did that with your Case statement, but not for everything. You scattered various other status actions throughout your code and it makes it easy to get confused. Get all statuses in that Case. and it becomes easier. Then some more tips:


1. Don't use a Dlookup to validate the user, use a DCount(). Dlookup can return a NULL if the username or password doesn't exist and I think if it does it will blow up your code. Dcount=1 valid user/password, Dcount=0 invalid.

2. Use the password the user submitted in the criteria of the DCount as well as the Username.

3. Put the criteria of the Dcount into a string, that way you can use it for the DCount to test a valid submission and also use it in a Dlookup to get the ID and TempPFlag values.

 

[inkbird01]

Hi Plog, thank you for your detailed reply. I had used the code I found on the internet .I am new to MS Access, i like your structure but no idea how to recreate it full work! any help appreciated

 

[isladogs]

You could also look at my example database which may help:

Password Login

This utility has been provided to manage user passwords and also keep a user session log

www.isladogs.co.uk

You should NEVER store passwords unless they are strongly encrypted

 

[inkbird01]

Thanks already have! all i need is something like


Private Sub cmd_login_Click()

' CHECK IF USERNAME OR PASSWORD FIELD IS BLANK

If IsNull(Me.txtloginID) Then
MsgBox "Username cannot be blank", vbInformation, "Please enter a username"
Me.txtloginID.SetFocus
ElseIf IsNull(Me.txtPassword) Then
MsgBox "Password cannot be blank", vbInformation, "Please enter a password"
Me.txtPassword.SetFocus
Else

' IF NOT BLANK CONTINUE

' CHECK IF USER HAS HAS THEIR PASSWORD RESET BY A SYSTEM ADMINISTRATOR AS INDICATED BY A '1' IN THE TEMPPFLAG COLUMN IN TBL_USER

Credentials.UserName = Me.txtloginID.Value
If DLookup("Password", "tbl_users", "UserName = '" & Credentials.UserName & "'") = Me.txtPassword ( ?something here to check if there is a '1' in the temPflag column? Then
Credentials.UserId = DLookup("ID", "tbl_users", "UserName = '" & Credentials.UserName & "'")
Credentials.AccessLvlID = DLookup("AccessLvl", "tbl_users", "UserName = '" & Credentials.UserName & "'")

 

[isladogs]

Thanks already have! all i need is something like

That's what I thought. Check my example app

 

[inkbird01]

That's what I thought. Check my example app

Thanks - the difference is that you use a combobox to store the flag value - I use a text box and only have the username. So should I create a invisble combo box that on the change event of the username textbox gets the flag status as its rowsource?

 

[isladogs]

You can use a textbox or combo in the admin form for new users.
All that is used is the value actually set in the control
Having a combo with Limit to List set ensures that only valid entries can be made

 

[inkbird01]

In the admin section i already have a mechanism that updates the tbl_users[temppflag] to 1 when the amdin resets the password.
What is not working is that when the user clicks the login button (after the admin has reset their password) - if the username entered has a tbl_user[temppflag] value of 1 then they must reset their password. If there is no '1', then continue

You can use a textbox or combo in the admin form for new users.
All that is used is the value actually set in the control
Having a combo with Limit to List set ensures that only valid entries can be made

 

[inkbird01]

Perhaps another way could be then there is a mechanism to check that when the user logs in, and their username has a temppflag of '1' then show the change password form - how could i achieve this?

Something like

If DCount("*", "tbl_Users", "username= '" & Me.txtloginID & "' AND Password = '" & Me.txtPassword & "'" & "TempPFlag" = 1) Then...

Bold part is what i am not sure to be correct?

 

[Gasman]

Put the criteria into a string variable and debug.print that. When you get it correct you can use the variable in the function.
You are at least missing an AND, temppflag should not be enclosed with quotes either.

Edit:
Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?

Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format

Numbers do not need anything

Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.

Added benefit is, if you still cannot see the error, you can copy and paste back here for someone else to spot it.

 

[Josef P.]

I would pick up the flag along with the password.

Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim StoredPassword As String
Dim TempPFlag As Boolean

Set db = CurrentDb

' use stored query:
' Set qdf = db.QueryDefs("YourQueryName")
' For testing a temporary querydef:
Set qdf = db.CreateQueryDef("", "parameters [@Username] text(255); select Password, TempPFlag from tbl_Users where username = [@Username]")
qdf.Parameters("@Username").Value = Credentials.UserName
With qdf.OpenRecordset(dbOpenForwardOnly)
   If Not .EOF Then
      StoredPassword = .Fields("Password").Value
      TempPFlag0 = .Fields("TempPFlag").Value
   Else
      ...
   End If
   .Close
End With
qdf.Close
Set qdf = Nothing

' 1. check password
...

' 2. check TempPFlag
...

 

[isladogs]

Excellent advice from @Gasman and @Josef P. As always.
I will just restrict my answer to fixing the DCount in case you want to try that

If DCount("*", "tbl_Users", "username= '" & Me.txtloginID & "' AND Password = '" & Me.txtPassword & "' AND TempPFlag = 1") >0 Then

 

[Josef P.]

I still throw BuildCriteria into the room

 

[Gasman]

I would pick up the flag along with the password.

Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim StoredPassword As String
Dim TempPFlag As Boolean

Set db = CurrentDb

' use stored query:
' Set qdf = db.QueryDefs("YourQueryName")
' For testing a temporary querydef:
Set qdf = db.CreateQueryDef("", "parameters [@Username] text(255); select Password, TempPFlag from tbl_Users where username = [@Username]")
qdf.Parameters("@Username").Value = Credentials.UserName
With qdf.OpenRecordset(dbOpenForwardOnly)
   If Not .EOF Then
      StoredPassword = .Fields("Password").Value
      TempPFlag0 = .Fields("TempPFlag").Value
   Else
      ...
   End If
   .Close
End With
qdf.Close
Set qdf = Nothing

' 1. check password
...

' 2. check TempPFlag
...

You could also get both with DLookUp() ?
Courtesy of @arnelgp

Help with Variant

HI! all I hope everyone is safe and sound, i have the below code on variant, how can i improve the code when i have say 10 variants and and 10 different ControlSource and other logic remains the same. Can anyone help with to provide me a better solution. I had the below code from my earlier...

www.access-programmers.co.uk

 

[Josef P.]

You could also get both with DLookUp() ?

With putting together and splitting the 2 values already. But why should you do that? (When separating you would have to consider that the separator could be contained in the password. => Use a character that cannot be typed with the keyboard.)
DLookup is not faster than a recordset. And if you split the code into its tasks, a well encapsulated structure comes out.

 

[Jason Lee Hayes]

CHECK IF USER HAS HAS THEIR PASSWORD RESET BY A SYSTEM ADMINISTRATOR

I personally dont beleive their should ever be a requirement for a system administrator to reset a password for an end user. Sure, have the ability to suspect/activate an account on end user request if end user is unable to change the password in a timely manor because of suspected compromise.

Giving system administrator the ability to create or change end user passwords is a no no..