VBA code only half working (1 Viewer)

Clayhead22 · Aug 13, 2017

Hi All

I have built a string of VBA code to reset a password if the user enters all of the correct details. This part works fine. The issue is that if the incorrect details are entered instead of retunring a MsgBox saying incorrect details enterd, it returns an access error Run-time error 3075. Code is below. please please please can someone help?

Code:

Private Sub Reset_Password_Click()
Dim sSql As String

If IsNull(Me.Username) Then
MsgBox "Username Required!"
Else
If IsNull(Me.Date_Of_Birth) Then
MsgBox "Date of Birth Required"
Else
If IsNull(Me.Email) Then
MsgBox "Email Address Required"
Else
If IsNull(Me.Mobile) Then
MsgBox "Mobile Number Required"
Else
If IsNull(Me.Post_Code) Then
MsgBox "Post Code Required"
Else
If IsNull(Me.New_Password) Then
MsgBox "New Password Required"
Else
If IsNull(Me.Confirm_Password) Then
MsgBox "Password Re-Enter Required"
Else

If (IsNull(DLookup("[Username]", "Users", "[Username] ='" & Me.Username.Value & "' And [Date of Birth] = #" & Me.Date_Of_Birth & "# And [Email] = '" & Me.Email.Value & "' And [Mobile] = '" & Me.Mobile.Value & "' And [Postal Code] = '" & Me.Post_Code.Value & "'"))) Then
MsgBox "Incorrect Details Entered"
Else


sSql = "Update Users Set [Password] = '" & Me.New_Password & "' WHERE [Username] =  '" & Me.Username & "' ;"

CurrentDb.Execute sSql, dbSeeChanges

MsgBox "Password Changed"

DoCmd.Close
DoCmd.OpenForm "Login"

End If
End If
End If
End If
End If
End If
End If
End If


End Sub

arnelgp · Aug 13, 2017

Try changing your code to this

Private Sub Reset_Password_Click()

If IsNull(Me.UserName) Then
Msgbox "UserName required!"
Me.UserName.SetFocus
Exit Sub
End if
If IsNull(Me.Date_Of_Birth) Then
MsgBox "Date of Birth required!"
Me.Date_Of_Birth.SetFocus
Exit Sub
End If
' do same with the rest validation
...
...
...
...
If Nz(DCount("*","Users","userName='" & Me.UserName & "'"), 0) = 0 Then
MsgBox "Incorrect Details Entered
Me.UserName.SetFocus
Exit Sub
End If

sSQL="Update Users Set Password='" & Me.New_Password * "' Where UserName ='" & Me.UserName & "'"
Currentdb.Execute sSQL, dbSeeChanges

MsgBox "Password Changed!"

DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "Login"

End Sub

ashleedawg · Aug 13, 2017

Ah yes, the good ol' 'Stack Overflow' error, which can be caused by various things; I'd describe it as usually caused by a coding error that uses up a part of memory that's set aside for remembering where the program is, with recursive processess... including nested If statements that aren't properly having a chance to "End"...

It looks like your if your nested If's are TRUE, the program isn't making it back to the End If statements, overloading the poor processor. (I could have that description completely wrong, but that's how I understand it!)

I would change the code to remove all those nested If's, something like below, then it's neat and today, and also can warn the user about errors on multiple fields at once.

Code:

Private Sub Reset_Password_Click()
Dim sSql As String, db As Database, sWarn As String

sWarn = ""
If IsNull(Me.username) Then sWarn = sWarn & "Username Required!" & vbCrLf
If IsNull(Me.Date_Of_Birth) Then sWarn = sWarn & "Date of Birth Required" & vbCrLf
If IsNull(Me.Email) Then sWarn = sWarn & "Email Address Required" & vbCrLf
If IsNull(Me.mobile) Then sWarn = sWarn & "Mobile Number Required" & vbCrLf
If IsNull(Me.Post_Code) Then sWarn = sWarn & "Post Code Required" & vbCrLf
If IsNull(Me.new_password) Then sWarn = sWarn & "New Password Required" & vbCrLf
If IsNull(Me.Confirm_Password) Then sWarn = sWarn & "Password Re-Enter Required" & vbCrLf

If sWarn <> "" Then 'something was wrong. Display the error(s)
    MsgBox sWarn
    Exit Sub
End If

If (IsNull(DLookup("[Username]", "Users", "[Username] ='" & Me.username.Value & "' And [Date of Birth] = #" & Me.Date_Of_Birth & _
    "# And [Email] = '" & Me.Email.Value & "' And [Mobile] = '" & Me.mobile.Value & "' And [Postal Code] = '" & Me.Post_Code.Value & "'"))) Then
        MsgBox "Incorrect Details Entered"
        Exit Sub
End If

'if we made it to this point then everything must be entered alright.
sSql = "Update Users Set [Password] = '" & Me.new_password & "' WHERE [Username] =  '" & Me.username & "' ;"
Set db = CurrentDb
db.Execute sSql, dbSeeChanges
If db.RecordsAffected <> 1 Then
    MsgBox "Error while changing password"
    Exit Sub
End If

MsgBox "Password Changed"

DoCmd.Close
DoCmd.OpenForm "Login"

End Sub

I also added a line to warn you if the password change affected zero (or more than one!) records. That's unrealted to the error, it's just a hangup of mine.

Clayhead22 · Aug 13, 2017

Excellent thanks for your quick responses guys. That works great.

isladogs · Aug 13, 2017

Glad its working but you might want to also trap for empty strings in each of the fields by using the Nz function. For example

Code:

If Nz(me.username,"")="" Then ....

VBA code only half working (1 Viewer)

Clayhead22

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

ashleedawg

"Here for a good time"

Clayhead22

Registered User.

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread