VBA to replace table record (1 Viewer)

[Clayhead22]

Hi All.

I have created a login form with a password reset function. Everything works with the exception of updating the password. The reset function looks up and confirms the pin exists in that users line on the table/

A form opens "Reset Password" and you need to enter your username, Pin and new password.

What i am struggling to do is, when you hit submit it looks up that user and replaces that users password with the new one they entered into the form. The names of the textboxes i have used are

Username
Pin
New Password.
Confirm Password.

I have been scrolling countless pages but dont seem to be able to find what i am looking for.

Ps i have explored queries to do this however want to do this through code. Can anybody help?

 

[Ranman256]

You use an update query.
This uses the items in the text box. Update the record where userID= forms!frmLogin!txtNewPass

 

[arnelgp]

Update UserTable Set Pin=[Forms]![Reset PassWord]![txtNewPassword] Where [UserName]=[Forms]![Reset PassWord]![txtUserName];

 

[Clayhead22]

Thanks for the heads up about the update query. I have tried implementing however i think i have done it wrong. Still getting used to VBA

#I get a sub or function not defined error.

Private Sub Change_Password_Click()
If IsNull(Me.Username) Then
MsgBox "Username Required!"
Else
If IsNull(Me.Pin) Then
MsgBox "Pin Required"
Else

If (IsNull(DLookup("[Username]", "Users", "[Username] ='" & Me.Username.Value & "' And [Pin] = '" & Me.Pin.Value & "'"))) Then
MsgBox "Incorrect Details Entered"
Else

Update Users
Set [Password] = Me.New_Password.Value
WHERE
[Username] = Me.Username.Value

MsgBox "Password Changed"


End If
End If
End If
End Sub

 

[Clayhead22]

ok I have read more and written the below in a blank query. Do you know what line of code to insert into the vba to run this in the background? Also does this look correct?

UPDATE Users SET [Password] = [Forms]![Reset Password Via Pin]![New Password
WHERE
[Username] = [Forms]![Reset Password Via Pin]![Username];

It comes up with unwanted confirmation boxes and also sets the table password value to a blank cell

 

[Minty]

I would use something like this - note I've avoided all those If Else constructs, they become very messy to debug if your code gets more complicated.

Private Sub Change_Password_Click()

Dim sSql as String


[COLOR="green"]'Check user input for nulls[/COLOR]
If IsNull(Me.Username) Or IsNull(Me.Pin) Then
	MsgBox "Username and/or Pin Required!"
	Exit sub 
End if

If (IsNull(DLookup("[Username]", "Users", "[Username] ='" & Me.Username & "' And [Pin] = '" & Me.Pin & "'"))) Then
	MsgBox "Incorrect Details Entered"
	Exit Sub
End IF

[COLOR="Green"]'Check for Blank or empty string new password ?[/COLOR]

If Len(Me.New_Password)& "" < 1 Then 
	MSgbox "New password cannot be blank!"
	Exit Sub
End If


[COLOR="green"]'User details correct so update[/COLOR]
sSql = "Update Users Set [Password] = '" & Me.New_Password & "' WHERE [Username] =  '" & Me.Username & "' ;"

CurrentDb.Execute sSql , dbSeeChanges

MsgBox "Password Changed"

End Sub

 

[Clayhead22]

Minty you are a legend thank you