Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-13-2017, 06:32 AM   #1
Clayhead22
Newly Registered User
 
Join Date: Feb 2015
Posts: 36
Thanks: 8
Thanked 0 Times in 0 Posts
Clayhead22 is on a distinguished road
VBA code only half working

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

Clayhead22 is offline   Reply With Quote
Old 08-13-2017, 07:25 AM   #2
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 3,970
Thanks: 47
Thanked 1,351 Times in 1,280 Posts
arnelgp has a spectacular aura about arnelgp has a spectacular aura about arnelgp has a spectacular aura about
Re: VBA code only half working

Try changing your code to this
Quote:
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
__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Clayhead22 (08-13-2017)
Old 08-13-2017, 07:29 AM   #3
ashleedawg
"Here for a good time"
 
ashleedawg's Avatar
 
Join Date: Jun 2017
Location: Canada
Posts: 150
Thanks: 18
Thanked 36 Times in 33 Posts
ashleedawg is on a distinguished road
Re: VBA code only half working

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.

__________________
_______________________________________
Back in the Fire
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by ashleedawg; 08-13-2017 at 07:37 AM.
ashleedawg is offline   Reply With Quote
The Following User Says Thank You to ashleedawg For This Useful Post:
Clayhead22 (08-13-2017)
Old 08-13-2017, 08:05 AM   #4
Clayhead22
Newly Registered User
 
Join Date: Feb 2015
Posts: 36
Thanks: 8
Thanked 0 Times in 0 Posts
Clayhead22 is on a distinguished road
Re: VBA code only half working

Excellent thanks for your quick responses guys. That works great.
Clayhead22 is offline   Reply With Quote
Old 08-13-2017, 08:11 AM   #5
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 2,034
Thanks: 36
Thanked 480 Times in 446 Posts
ridders will become famous soon enough ridders will become famous soon enough
Re: VBA code only half working

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 ....

__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
, Tapatalk


If this answer has helped, please click the Thanks button and/or click the 'reputation' scales symbol on the left and leave a comment. Cheers!
ridders is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Export to Excel working, but Access query used is not working thru VBA code Keevin77 Modules & VBA 11 05-06-2014 11:39 AM
Only half of if statement working fixemup27 Forms 3 04-16-2012 12:27 PM
Send keys stops working half way a.phillips Modules & VBA 11 12-21-2011 07:59 AM
Working Dlookup code not working Gazza2 Modules & VBA 16 02-28-2011 04:56 PM
Half way there sportsguy Modules & VBA 0 03-30-2005 07:52 PM




All times are GMT -8. The time now is 07:07 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World