Precursor: "I have searched for several hours and tried a lot of different methods so I apologize if this has been answered"
I have a Log-in required database that I am adding a change password form to. The form works with an exception of one error; I cannot close the form after changing the password. I receive the error: This action can't be carried out while processing a form or report event. (2585)
Table tbl_Users -
ID AutoNumber
-
UserName Short Text
-
Password Short Text
-
Role Short text
Form frm_ChangePass Values -
oldpass (unbound textbox refers to password in field)
-
newpass (unbound textbox refers to new pass)
-
confirmpass (unbound textbox used to validate new pass)
Form frm_ChangePass VBA -
Dim strUpdateSQL As String
-
Dim strCUser As String
-
-
strCUser = strUser
-
newpass.SetFocus
-
-
DoCmd.SetWarnings False
-
-
strUpdateSQL = "UPDATE tbl_Users SET Password ='" & Me.newpass.Value & "' WHERE UserName ='" & strCUser & "'"
-
DoCmd.RunSQL strUpdateSQL
-
-
DoCmd.SetWarnings True
-
-
DoCmd.Close acForm, "frm_ChangePass", acSaveNo
-
DoCmd.OpenForm "frm_Menu", acNormal, "", "", , acNormal
I'll leave out the If/Else shell unless you want to see it.
**strUser is a global string set on log-in.
18 2256 zmbd 5,501
Expert Mod 4TB
For trouble shooting Comment out line 7 and 12
Insert after line 9
Run your code,
Report any additional errors or unusual messages
Press <ctrl><g> and do a cut and paste of the resulting string, please remember to format it as code.
Steven,
I think the problem is in how you got to this code. What event is this code within? Whatever the chain of events has been, you are not allowed to close the form from here. Remove the close form from this code and choose another way to force the form to close after the password changes.
Better yet, explain the event sequence and maybe we can learn from your experience as we try to nail down precisely what happened.
Jim
zmbd
The only thing I receive when taking of the warning messages is the SQL prompt to verify I want to change 1 record. The SQL printout is below. -
UPDATE tbl_Users SET Password ='password' WHERE UserName ='Administrator'
jimatqsi
This code is invoked by a button on frm_Menu the opens frm_ChangePass and closes frm_Menu. The process for changing the password is below. -
IsNull([oldpadd])
-
IsNull([newpass])
-
IsNull([confirmpass])
-
-
Me.oldpass.Value = DLookup("Password", "tbl_Users", "[UserName]='" & strUser & "'")
-
-
If Me.newpass = Me.confirmpass
-
-
DoCmd.RunSQL strUpdateSQL
-
DoCmd.Close acForm, "frm_ChangePass", acSaveNo
-
DoCmd.OpenForm "frm_Menu", acNormal, "", "", , acNormal
-
-
(close all Ifs)
If you need more than pseudo code just let me know.
Maybe you need to open frm_Menu before closing frm-ChangePass (duh on all of us).
Jim
Unfortunately that doesn't change anything. Tried that a long time ago.
Here is the full shebang... -
Public Sub ChangePass()
-
-
-
If IsNull([oldpass]) = True Then 'Check Old Pass
-
MsgBox "Old Password required"
-
-
ElseIf IsNull([newpass]) = True Then 'Check Password
-
MsgBox "New password is required"
-
-
ElseIf IsNull([confirmpass]) = True Then 'Check Password
-
MsgBox "Please confirm password"
-
-
Else
-
-
'Compare value of txtPassword with the saved Password in tblUser
-
If Me.oldpass.Value = DLookup("Password", "tbl_Users", "[UserName]='" & strUser & "'") Then
-
-
If Me.newpass = Me.confirmpass Then 'Compare two new passwords
-
-
Dim strUpdateSQL As String
-
Dim strCUser As String
-
strCUser = strUser
-
newpass.SetFocus
-
-
DoCmd.SetWarnings False 'off Warnings
-
-
'set SQL
-
strUpdateSQL = "UPDATE tbl_Users SET Password ='" & Me.newpass.Value & "' WHERE UserName ='" & strCUser & "'"
-
DoCmd.RunSQL strUpdateSQL
-
-
DoCmd.SetWarnings True 'on Warnings
-
-
'close/open forms
-
DoCmd.Close acForm, "frm_ChangePass", acSaveNo
-
DoCmd.OpenForm "frm_Menu", acNormal, "", "", , acNormal
-
-
-
Else
-
-
MsgBox "The password's do not match"
-
Exit Sub
-
-
End If 'end new confirm passwords If
-
-
Else
-
MsgBox "Invalid Password. Please try again.", vbOKOnly, "Invalid Password"
-
oldpass.SetFocus
-
-
End If 'end confirm old password If
-
-
End If 'end confirm non null value's If
-
-
-
End Sub
Yea, I'm surprised, I just tried that and it works fine to close an object you're within and still do stuff like open another object.
Well, did you try zmbd's suggestion? I think we need to follows his common sense trouble-shooting approach and see where it takes us.
Jim
Yeah, I believe zmbd was focusing on maybe an SQL error, but right now that seems to be doing fine. From prior research it seems to be a problem within the SQL not being done and the form trying to close. I am going to move the operations to a public function on my "public functions module" and see if that makes a difference. This might allow the module to perform the change and form to close... I'll update.
-Stevan
Tried the public function method and no luck... Not really sure where to head from here.
Did you step through with the debugger? Step through all the events that fire when you do that form close. It may become obvious when you do that.
Jim
Thx Jim, unfortunately the only error still arising is the error on close but I made a work around for now because I am on a short deadline with little room to experiment. Basically instead of closing the form I just hid it when opening the menu and have it set to refresh after it performs the SQL to make it work properly. Not the best method but it will work until I have more time to experiment. See below. -
Public Sub changePass()
-
-
Dim LResult As Long
-
-
LResult = Len(Me.newpass)
-
-
If IsNull(Me.oldpass) = True Then 'Check Old Pass
-
MsgBox "Old Password required"
-
-
ElseIf IsNull(Me.newpass) = True Then 'Check Password
-
MsgBox "New password is required"
-
-
ElseIf IsNull(Me.confirmpass) = True Then 'Check Password
-
MsgBox "Please confirm password"
-
-
ElseIf LResult < 8 Then
-
MsgBox "Password must be at least eight characters"
-
-
Else
-
-
'Compare value of txtPassword with the saved Password in tblUser
-
If Me.oldpass.Value = DLookup("Password", "tbl_Users", "[UserName]='" & strUser & "'") Then
-
-
If Me.newpass.Value = Me.confirmpass.Value Then 'Compare two new passwords
-
-
Dim strUpdateSQL As String
-
Dim strCUser As String
-
strCUser = strUser
-
-
DoCmd.SetWarnings False 'off Warnings
-
-
'set SQL
-
strUpdateSQL = "UPDATE tbl_Users SET Password ='" & Me.newpass.Value & "' WHERE UserName ='" & strCUser & "'"
-
DoCmd.RunSQL strUpdateSQL, False
-
DBEngine.Idle dbRefreshCache
-
-
DoCmd.SetWarnings True 'on Warnings
-
-
'close/open forms
-
oldpass.Value = ""
-
newpass.Value = ""
-
confirmpass.Value = ""
-
DoCmd.OpenForm "frm_ChangePass", , , , , acHidden
-
MsgBox "Password changed for " & strUser & " successfully.", vbOKOnly
-
DoCmd.OpenForm "frm_Menu", acNormal, "", "", , acNormal
-
-
Else
-
-
MsgBox "The password's do not match"
-
Exit Sub
-
-
End If 'end new confirm passwords If
-
-
Else
-
MsgBox "Invalid Password. Please try again.", vbOKOnly, "Invalid Password"
-
oldpass.SetFocus
-
-
End If 'end confirm old password If
-
-
End If 'end confirm non null value's If
-
-
End Sub
zmbd 5,501
Expert Mod 4TB
Basically instead of closing the form I just hid it when opening the menu and have it set to refresh after it performs the SQL to make it work properly.
As I suspected, your code is closing the form too soon.
What I would have had you do next is instert a stop command (not a break point as given by the red dot (^_^) ) and then SLOWLY, EVERY SO SLOWLY F8 thru the code. Usually if one waits between the F8 steps, the code will execute, then the next line that closes the form runs just fine...
I will usually hide a form that any code refers to for parameters and check that the records have been added or updated before closing the form.
(^_^)
NeoPa 32,556
Expert Mod 16PB
Hi Stevan.
You might like to try the .Execute method of the database object (probably with the dbFailOnError parameter). This works synchronously so the update will always complete before the next line of VBA code runs. As a bonus, if there is any chance that the update will fail then you get the chance of capturing that and handling it.
Thank you all for your help!! Glad I could at least move forward, (had an initial preview on Saturday so I was a bit stressed on time). NeoPa! I remember when you helped me in my earlier years :) (like 2011) I will definitely try that and see what the wait time is. It will be good for future projects too. I probably won't add it to this function simply because waiting 15 seconds to have a password change is a bit excessive. But for full record updates and longer SQL that will be a good method to implement.
NeoPa 32,556
Expert Mod 16PB
I remember you too Stevan. In the military if I remember correctly :-)
Anyway, I can't say how long the delay will be for you, but setting a password on a single record in a table should take less than a second in any scenario I can think of.
Of course, I'd always strongly recommend that it is not stored as is but encrypted first. We have a number of articles on encryption routines here on Bytes.
Best wishes -Adrian.
That is correct! (not anymore though) I will definitely look into encryption. That sounds like a good plan for this project.
Now on to my next question.. (coming soon) Edit:
NVM... Went back to an old question of mine and figured it out.
NeoPa 32,556
Expert Mod 16PB
Sorry Stevan. Had to reset that. We only assign to OP in rare cases and we need the answer to be a good one for the question in all cases.
Understandable. :) I will look more into it when I have time to dive into it.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: R Baumann |
last post by:
I'm a newbie with Python, and trying to connect to the Interbase/Firebird
'EMPLOYEE.GDB' file to play with it.
Can anyone help with this?
I'm running Firebird 1.5rc4
Python 2.3.2
kinterbasdb...
|
by: Steve Thorpe |
last post by:
Hi
I am trying to write a report that calculates the average number of sales
over 7, 14, 31 and 365 days for each hourly period of the day. the problem
is it takes over 4 minutes to run.
...
|
by: Bill S. |
last post by:
Hi,
I a stored procedure that inserts a record into a table as
below.
The insert works OK, but if the insert violates a unique
indewx constraint on one of the columns, the proc terminates...
|
by: annie |
last post by:
Hi all,
I have recently ported my Access 2000 app to SQL Server, keeping the
Access client as the front end using linked tables.
I am also using triggers on my SQL tables to trap orphan...
|
by: Andrew Thackray |
last post by:
Can anyone explain this error. I have the following code in a button on a form
Private Sub cmdExit_Click()
DoCmd.Close acForm, "ActivityMaintenance"
End Sub
|
by: db2group88 |
last post by:
we had this error while running the applicaiton on db2 v8.2 EE on
windows, jcc driver is what we use
SQL error 805
com.ibm.db2.jcc.b.SQLException: NULLID.SYSLH203 0X5359534C564C3031,
based on...
|
by: kev |
last post by:
Hi folks,
I have a form for registration (frmRegistration) whereby i have two
buttons. One is Save which saves record using the OnClick property. I
used wizard to create the save button. The...
|
by: Bobby |
last post by:
Hi
I'm using Access 2003 with SQL server 2000, linked via ODBC.
Can anybody tell me how to capture SQL error codes in Access? If this
is not possible, is there any way I can simply turn off SQL...
|
by: OldBirdman |
last post by:
I want to open a form, let's call it fSelect, with some optional choices on it. If the operator makes a choice, that choice is saved to a control on the main form, and fSelect is closed.
However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |