Login Form - Check if password reset flag before continuing. (1 Viewer)

inkbird01

New member
Local time
Today, 08:53
Joined
Oct 5, 2022
Messages
20
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


Code:
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

Banishment Pending
Local time
Today, 02:53
Joined
May 11, 2011
Messages
11,694
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:

Code:
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.
 
Last edited:

inkbird01

New member
Local time
Today, 08:53
Joined
Oct 5, 2022
Messages
20
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

MVP / VIP
Local time
Today, 08:53
Joined
Jan 14, 2017
Messages
18,295
You could also look at my example database which may help:

You should NEVER store passwords unless they are strongly encrypted
 

inkbird01

New member
Local time
Today, 08:53
Joined
Oct 5, 2022
Messages
20
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 & "'")
 

inkbird01

New member
Local time
Today, 08:53
Joined
Oct 5, 2022
Messages
20
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?
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 08:53
Joined
Jan 14, 2017
Messages
18,295
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

New member
Local time
Today, 08:53
Joined
Oct 5, 2022
Messages
20
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

New member
Local time
Today, 08:53
Joined
Oct 5, 2022
Messages
20
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?
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 08:53
Joined
Sep 21, 2011
Messages
14,607
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. :)
 
Last edited:

Josef P.

Well-known member
Local time
Today, 09:53
Joined
Feb 2, 2023
Messages
887
I would pick up the flag along with the password.
Code:
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

MVP / VIP
Local time
Today, 08:53
Joined
Jan 14, 2017
Messages
18,295
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

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

Gasman

Enthusiastic Amateur
Local time
Today, 08:53
Joined
Sep 21, 2011
Messages
14,607
I would pick up the flag along with the password.
Code:
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
 

Josef P.

Well-known member
Local time
Today, 09:53
Joined
Feb 2, 2023
Messages
887
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. ;)
 
Last edited:

Jason Lee Hayes

Active member
Local time
Today, 08:53
Joined
Jul 25, 2020
Messages
187
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..
 

Users who are viewing this thread

Top Bottom