I am new to this site and NO EXPERT! by any means, but I just did something similar at work so it may help you:
On your CHANGE PASS form:
include a hidden listbox [lstbox_PASSCHECK]
lstbox_PASSCHECK will have a rowsource of a Query [qry_FIND_PASSWORD]
that looks for the password with the username box [txtUsername] as a criteria. Assuming that no one user has multiple passwords, the [qryFIND_PASSWORD] should return only 1 value. Now make sure you remember to update that Query after [txtUsername] is changed. (under the afterupdate section of the events property you can just write me.lstbox_PASSCHECK.Requery). This makes sure that you get the appropriate password everytime you change user names.
Also set lstbox_PASSCHECK value to lstbox_PASSCHECK.ItemData(0). This puts the first value of your query into the value of the lstbox. You can also create a new textbox (also hidden) and just pass it the value lstbox_PASSCHECK.ItemData(0)
I used the second method, but I think both should work.
Now that you have all that done!
You need to write an update query that will change the value in the Login table with the username [txtUsername], as a criteria. It will update to the value of the user inputed NEWPASSWORD [txtPass1]. If you don't know the path, just use the "builder" function on the "update to" section of the update query. We will call that query, "Update_Password"
Lastly!
You will write code that is a basic If... Then statement saying:
If me.pwdLastSet=me.lstbox_PASSCHECK.value AND me.txtPass1=txtPass2.Text Then
DoCmd.OpenQuery "Update_Password"
MsgBox "Your password has been changed!"
' here you want to clear you textboxes, listboxes, etc. me.txtbox.value = Null
DoCmd.Close acForm "Change Pass"
Else
MsgBox "Your password is incorrect."
me.pwdLastSet.SetFocus ' This just puts the user back to the OldPassword txtbox
End If
Don't forget to clear the values for the lstbox_PASSCHECK and the username, oldpassword, newpassword, etc.
This is probably a hack way at doing it, and I am sure there are cleaner better ways out there, but this one worked for me. Experiment with it and let me know if it helps at all!
(perhaps you should use userID instead of username because of multiple identical names? or maybe a combination of both? Sorry, maybe you have already done that and I didn't see. Just a thought.)
Hope this all helps!
MR_G