Solved Reset password screen (1 Viewer)

mistermarty

Member
Local time
Today, 23:06
Joined
Oct 31, 2020
Messages
41
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

AWF VIP
Local time
Today, 23:06
Joined
Jul 26, 2013
Messages
10,355
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

Enthusiastic Amateur
Local time
Today, 23:06
Joined
Sep 21, 2011
Messages
14,048
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? :unsure: 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

Immoderate Moderator
Staff member
Local time
Today, 18:06
Joined
Feb 28, 2001
Messages
27,001
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

Member
Local time
Today, 23:06
Joined
Oct 31, 2020
Messages
41
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.

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

Gasman

Enthusiastic Amateur
Local time
Today, 23:06
Joined
Sep 21, 2011
Messages
14,048
Need the sql from "qryUpdatePassword" ?
 

mistermarty

Member
Local time
Today, 23:06
Joined
Oct 31, 2020
Messages
41
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? :unsure: 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 :)
 

Attachments

  • Screen1.jpg
    Screen1.jpg
    145.9 KB · Views: 230
  • Screen2.jpg
    Screen2.jpg
    106.3 KB · Views: 233
  • Screen3.jpg
    Screen3.jpg
    137.1 KB · Views: 226
  • Screen4.jpg
    Screen4.jpg
    143.4 KB · Views: 239

Gasman

Enthusiastic Amateur
Local time
Today, 23:06
Joined
Sep 21, 2011
Messages
14,048
Yes
Include
Code:
WHERE Username = """ & Me.txt_Username & """
as you did for looking up the password.
 

mistermarty

Member
Local time
Today, 23:06
Joined
Oct 31, 2020
Messages
41
Yes
Include
Code:
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
 

Attachments

  • Screen5.jpg
    Screen5.jpg
    152 KB · Views: 242

Gasman

Enthusiastic Amateur
Local time
Today, 23:06
Joined
Sep 21, 2011
Messages
14,048
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.
 

mistermarty

Member
Local time
Today, 23:06
Joined
Oct 31, 2020
Messages
41
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 (y):)
 
Last edited:

mistermarty

Member
Local time
Today, 23:06
Joined
Oct 31, 2020
Messages
41
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...?

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

Enthusiastic Amateur
Local time
Today, 23:06
Joined
Sep 21, 2011
Messages
14,048
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

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

Super Moderator
Staff member
Local time
Today, 19:06
Joined
Feb 19, 2002
Messages
42,977
Here is a working example if you still need it.
 

Attachments

  • SwitchboardForm20201104.zip
    1.6 MB · Views: 260

mistermarty

Member
Local time
Today, 23:06
Joined
Oct 31, 2020
Messages
41
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

Code:
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! o_O
Cheers for trying to help me.
M
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:06
Joined
Sep 21, 2011
Messages
14,048
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! o_O
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
Code:
UPDATE tblUsers SET tblUsers.[Password] = [Forms]![frmResetPassword]![txt_NewPassword]
WHERE tblUsers.Username=[Forms]![frmResetPassword]![txt_UserName];
 

mistermarty

Member
Local time
Today, 23:06
Joined
Oct 31, 2020
Messages
41
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
Code:
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

MVP / VIP
Local time
Today, 23:06
Joined
Jan 14, 2017
Messages
18,186
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! :rolleyes:

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

Users who are viewing this thread

Top Bottom