Solved Reset password screen

[mistermarty]

Ok friends. I need some help… I’m new to this so please explain it as though you were instructing a young child! This is to do with user log-in details for my database.

I have a table called tblUsers, with the usual stuff like ID, Name, UserName, Password fields etc. I have managed to get the logging in part working, but I would like to have the facility to set a new user’s password to something like “password” and force them to change it when they log on for the first time.

I have a form called frmLogIn which is used to log into the database, and another form called frmResetPassword for the process of choosing a new password. When the user logs in for the first time, the login form checks what the current password is, and if it is “password” the frmResetPassword form automatically opens up; if not, the database opens up as normal.

At the moment, my frmResetPassword form has 4 unbound text boxes: Username, Current Password, New Password and Re-type Password. It also has 2 buttons: Confirm and Cancel.

Here’s where I need help. I need to get Access to check the UserName and Password field against the tblUsers, and if they are correct, enable the user to input a new password. The new password needs to be entered in the New Password box and the Re-type Password boxes, and these have to match before the change is accepted. Finally, when the user clicks the Confirm button, it should update the tblUsers with the new password.

I have managed to get a bit further with this, but when I get to submitting the change password screen, it changes ALL of the user passwords that are set to "password" (using an update query). How do I get it to just change the password for the specific usename? Below, I have attached some screenshots in sequence, and the code attached to the change password submit button. Thanks again.

Cheers

 

[Minty]

Please copy and paste your code into a post using the code tags ( </> button above in the editor )
A picture is no use for spotting errors or typo's in code, and can't be copied into a test environment or editor either.

 

[Gasman]

Just from your question, you need criteria in the query sql?

WHERE [userid]= Me.UserId

Your post appears to have all the pictures linked to Facebook? so that was likely your posting problem.?
You can just paste here as I mentioned or upload as attachments.
I cannot even read those pictures and the links just result in an error message in FB.

 

[The_Doc_Man]

As a secondary bit of advice, you could search this forum for the topic of Login Screens or Login Forms to see how many others have approached this problem. Searching might bring you a lot of articles. But you could even look at the thread titles in the "Similar Threads" section that appears below your thread. Once you have a thread with a relevant title, the forum software can find similar threads for you.

 

[mistermarty]

Please copy and paste your code into a post using the code tags ( </> button above in the editor )
A picture is no use for spotting errors or typo's in code, and can't be copied into a test environment or editor either.

Here it is. I can't take any credit for putting it together as it's something I came across when searching for some answers.

Private Sub Command4_Click()

  Dim db As DAO.Database
  Dim rst As DAO.Recordset
  Dim strSQL As String
 
  If Trim(Me.txt_Username.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Username should not be left blank.", buttons:=vbInformation, title:="Username Required"
    Me.txt_Username.SetFocus
    Exit Sub
  End If
 
  If Trim(Me.txt_OldPassword.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Password should not be left blank.", buttons:=vbInformation, title:="Password Required"
    Me.txt_OldPassword.SetFocus
    Exit Sub
  End If
 
  'query to check if login details are correct
  strSQL = "SELECT FirstName FROM tblUsers WHERE Username = """ & Me.txt_Username.Value & """ AND Password = """ & Me.txt_OldPassword.Value & """"
 
  Set db = CurrentDb
  Set rst = db.OpenRecordset(strSQL)
  If rst.EOF Then
    MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, title:="Login Error"
    Me.txt_Username.SetFocus
  Else
  DoCmd.OpenQuery "qryUpdatePassword"
 
    End If

  DoCmd.Close
  DoCmd.OpenForm "frmSwitchboard"

End Sub

 

[theDBguy]

How do I get it to just change the password for the specific usename?

Make sure to have the user's ID in your UPDATE query.

 

[Gasman]

Need the sql from "qryUpdatePassword" ?

 

[mistermarty]

Just from your question, you need criteria in the query sql?

WHERE [userid]= Me.UserId

Your post appears to have all the pictures linked to Facebook? so that was likely your posting problem.?
You can just paste here as I mentioned or upload as attachments.
I cannot even read those pictures and the links just result in an error message in FB.

That's just weird! I can't think why it links them to FB. I've attached the screenshots to this message and the VBA code is on my reply to Minty above.
So from your suggestion, am I right in thinking that it needs to be included in the Update Query expression builder?
Cheers

 

[Gasman]

Yes
Include

WHERE Username = """ & Me.txt_Username & """

as you did for looking up the password.

 

[mistermarty]

Yes
Include

WHERE Username = """ & Me.txt_Username & """

as you did for looking up the password.

Thanks, I really appreciate you taking the time to help. So sorry if this is really basic stuff, but I can't figure out exactly where to include your code. My update query looks like the attached screenshot...
Apologies for being a biff!
M

 

[Gasman]

Drag Username to another column and use the same syntax as you have for the Password, but put it in the criteria row. Then choose SQL view so you can see what has been created.

Please copy and post your code within code tags as per my sig, instead of all these pictures. Much easier to read.

 

[isladogs]

You are welcome to use my free password login app which includes the feature you want:
i.e. force all new users to change the default password on first login.
As an option, you can also require users to reset their password after a specified number of days.
See http://www.mendipdatasystems.co.uk/password-login/4594469149

 

[mistermarty]

You are welcome to use my free password login app which includes the feature you want:
i.e. force all new users to change the default password on first login.
As an option, you can also require users to reset their password after a specified number of days.
See http://www.mendipdatasystems.co.uk/password-login/4594469149

Hi. Thanks for your reply. Wow, that definitely looks along the lines of what I'm after. Do I need to do anything special to use it? The reason I ask is that I opened it up and tried logging in as Jill. As mentioned in your description, I was prompted to select a new password, but when I did, I got message: "Error 3073 in txtConPWD_AfterUpdateprocedure: Operation must use an updatable query". Then when the Login button became active and I clicked it, I got "Error 3061 in cmdLogin_Click procedure: Too few parameters. Expected 1".
Cheers
M

Edit: Ignore the above. It was because I hadn't saved a copy of the database after downloading it. Nice work! I think this is something I can use. Thanks

 

[mistermarty]

Drag Username to another column and use the same syntax as you have for the Password, but put it in the criteria row. Then choose SQL view so you can see what has been created.

Please copy and post your code within code tags as per my sig, instead of all these pictures. Much easier to read.

Am I heading the right way with this...?

UPDATE tblUsers SET tblUsers.[Password] = [Forms]![frmResetPassword]![txt_NewPassword], tblUsers.Username = [Forms]![frmResetPassword]![txt_NewPassword]
WHERE (((tblUsers.Password)=[Forms]![frmResetPassword]![txt_OldPassword]) AND ((tblUsers.Username)=""" & Me.txt_Username & """;

 

[Gasman]

I do not see any need to check the password?
Hopefully there is only one password per user?

You have copied two columns to the Query GUI when I said just the one for Username?

It should be more like

UPDATE tblUsers SET tblUsers.[Password] = [Forms]![frmResetPassword]![txt_NewPassword]
WHERE tblUsers.Username=""" & Me.txt_Username & """;

Copy that back into the SQL window then go back to design view.
Access always adds enough parentheses to surround the world.

 

[Pat Hartman]

Here is a working example if you still need it.

 

[mistermarty]

I do not see any need to check the password?
Hopefully there is only one password per user?

You have copied two columns to the Query GUI when I said just the one for Username?

It should be more like

UPDATE tblUsers SET tblUsers.[Password] = [Forms]![frmResetPassword]![txt_NewPassword]
WHERE tblUsers.Username=""" & Me.txt_Username & """;

Copy that back into the SQL window then go back to design view.
Access always adds enough parentheses to surround the world.

Hmm... that's changed it so as it now doesn't change any of the passwords. Might have to leave it for a while and come back to it. Starting to fry my brain!
Cheers for trying to help me.
M

 

[Gasman]

Hmm... that's changed it so as it now doesn't change any of the passwords. Might have to leave it for a while and come back to it. Starting to fry my brain!
Cheers for trying to help me.
M

Well now it will only update if it finds a matching username?

Ok, my bad. I was thinking it was in a form, so you have to use the syntax that you used for the password. I lnow, I'm repeating myself here. :-(
Try

UPDATE tblUsers SET tblUsers.[Password] = [Forms]![frmResetPassword]![txt_NewPassword]
WHERE tblUsers.Username=[Forms]![frmResetPassword]![txt_UserName];

 

[mistermarty]

Well now it will only update if it finds a matching username?

Ok, my bad. I was thinking it was in a form, so you have to use the syntax that you used for the password. I lnow, I'm repeating myself here. :-(
Try

UPDATE tblUsers SET tblUsers.[Password] = [Forms]![frmResetPassword]![txt_NewPassword]
WHERE tblUsers.Username=[Forms]![frmResetPassword]![txt_UserName];

B-I-N-G-O!! That worked a treat!!! Thanks for your endless patience. I can actually see where the problem was too, which is great.
On to the next task...
Thanks again buddy
M

 

[isladogs]

Just saw post #13 together with your edit. Pleased to see you got it working without me having to check anything.....
When you originally got those errors, were you trying to run it from the zip file? That won't work for any Access file!

Anyway, it seems you've now got yours working with the help of Gasman