By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,623 Members | 1,951 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,623 IT Pros & Developers. It's quick & easy.

Close form after running SQL: Error 2585

Brilstern
100+
P: 208
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
Expand|Select|Wrap|Line Numbers
  1. ID         AutoNumber
  2. UserName   Short Text
  3. Password   Short Text
  4. Role       Short text
Form frm_ChangePass Values
Expand|Select|Wrap|Line Numbers
  1. oldpass (unbound textbox refers to password in field)
  2. newpass (unbound textbox refers to new pass)
  3. confirmpass (unbound textbox used to validate new pass)

Form frm_ChangePass VBA
Expand|Select|Wrap|Line Numbers
  1. Dim strUpdateSQL As String
  2. Dim strCUser As String
  3.  
  4. strCUser = strUser
  5. newpass.SetFocus
  6.  
  7.     DoCmd.SetWarnings False
  8.  
  9.          strUpdateSQL = "UPDATE tbl_Users SET Password ='" & Me.newpass.Value & "' WHERE UserName ='" & strCUser & "'"
  10.          DoCmd.RunSQL strUpdateSQL
  11.  
  12.     DoCmd.SetWarnings True
  13.  
  14.          DoCmd.Close acForm, "frm_ChangePass", acSaveNo 
  15.          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.
Apr 11 '14 #1
Share this Question
Share on Google+
18 Replies


zmbd
Expert Mod 5K+
P: 5,397
For trouble shooting Comment out line 7 and 12
Insert after line 9
Expand|Select|Wrap|Line Numbers
  1. debug.print strUpdateSQL  
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.
Apr 11 '14 #2

Expert 100+
P: 1,240
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
Apr 11 '14 #3

Brilstern
100+
P: 208
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.

Expand|Select|Wrap|Line Numbers
  1. 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.

Expand|Select|Wrap|Line Numbers
  1. IsNull([oldpadd])
  2. IsNull([newpass])
  3. IsNull([confirmpass])
  4.  
  5. Me.oldpass.Value = DLookup("Password", "tbl_Users", "[UserName]='" & strUser & "'")
  6.  
  7.     If Me.newpass = Me.confirmpass
  8.  
  9.           DoCmd.RunSQL strUpdateSQL
  10.           DoCmd.Close acForm, "frm_ChangePass", acSaveNo
  11.           DoCmd.OpenForm "frm_Menu", acNormal, "", "", , acNormal
  12.  
  13. (close all Ifs)
If you need more than pseudo code just let me know.
Apr 11 '14 #4

Expert 100+
P: 1,240
Maybe you need to open frm_Menu before closing frm-ChangePass (duh on all of us).

Jim
Apr 11 '14 #5

Brilstern
100+
P: 208
Unfortunately that doesn't change anything. Tried that a long time ago.
Apr 11 '14 #6

Brilstern
100+
P: 208
Here is the full shebang...

Expand|Select|Wrap|Line Numbers
  1. Public Sub ChangePass()
  2.  
  3.  
  4.     If IsNull([oldpass]) = True Then 'Check Old Pass
  5.         MsgBox "Old Password required"
  6.  
  7.     ElseIf IsNull([newpass]) = True Then 'Check Password
  8.         MsgBox "New password is required"
  9.  
  10.     ElseIf IsNull([confirmpass]) = True Then 'Check Password
  11.         MsgBox "Please confirm password"
  12.  
  13.     Else
  14.  
  15.         'Compare value of txtPassword with the saved Password in tblUser
  16.         If Me.oldpass.Value = DLookup("Password", "tbl_Users", "[UserName]='" & strUser & "'") Then
  17.  
  18.             If Me.newpass = Me.confirmpass Then 'Compare two new passwords
  19.  
  20.                 Dim strUpdateSQL As String
  21.                 Dim strCUser As String
  22.                 strCUser = strUser
  23.                 newpass.SetFocus
  24.  
  25.                 DoCmd.SetWarnings False 'off Warnings
  26.  
  27.                     'set SQL
  28.                     strUpdateSQL = "UPDATE tbl_Users SET Password ='" & Me.newpass.Value & "' WHERE UserName ='" & strCUser & "'"
  29.                     DoCmd.RunSQL strUpdateSQL
  30.  
  31.                 DoCmd.SetWarnings True 'on Warnings
  32.  
  33.                 'close/open forms
  34.                 DoCmd.Close acForm, "frm_ChangePass", acSaveNo
  35.                 DoCmd.OpenForm "frm_Menu", acNormal, "", "", , acNormal
  36.  
  37.  
  38.             Else
  39.  
  40.                 MsgBox "The password's do not match"
  41.                 Exit Sub
  42.  
  43.             End If 'end new confirm passwords If
  44.  
  45.         Else
  46.             MsgBox "Invalid Password. Please try again.", vbOKOnly, "Invalid Password"
  47.             oldpass.SetFocus
  48.  
  49.         End If 'end confirm old password If
  50.  
  51.     End If 'end confirm non null value's If
  52.  
  53.  
  54. End Sub
Apr 11 '14 #7

Expert 100+
P: 1,240
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
Apr 11 '14 #8

Brilstern
100+
P: 208
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
Apr 11 '14 #9

Brilstern
100+
P: 208
Tried the public function method and no luck... Not really sure where to head from here.
Apr 11 '14 #10

Expert 100+
P: 1,240
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
Apr 12 '14 #11

Brilstern
100+
P: 208
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.

Expand|Select|Wrap|Line Numbers
  1. Public Sub changePass()
  2.  
  3. Dim LResult As Long
  4.  
  5. LResult = Len(Me.newpass)
  6.  
  7. If IsNull(Me.oldpass) = True Then 'Check Old Pass
  8.         MsgBox "Old Password required"
  9.  
  10.     ElseIf IsNull(Me.newpass) = True Then 'Check Password
  11.         MsgBox "New password is required"
  12.  
  13.     ElseIf IsNull(Me.confirmpass) = True Then 'Check Password
  14.         MsgBox "Please confirm password"
  15.  
  16.     ElseIf LResult < 8 Then
  17.         MsgBox "Password must be at least eight characters"
  18.  
  19.     Else
  20.  
  21.         'Compare value of txtPassword with the saved Password in tblUser
  22.         If Me.oldpass.Value = DLookup("Password", "tbl_Users", "[UserName]='" & strUser & "'") Then
  23.  
  24.             If Me.newpass.Value = Me.confirmpass.Value Then 'Compare two new passwords
  25.  
  26.                 Dim strUpdateSQL As String
  27.                 Dim strCUser As String
  28.                 strCUser = strUser
  29.  
  30.                 DoCmd.SetWarnings False 'off Warnings
  31.  
  32.                     'set SQL
  33.                     strUpdateSQL = "UPDATE tbl_Users SET Password ='" & Me.newpass.Value & "' WHERE UserName ='" & strCUser & "'"
  34.                     DoCmd.RunSQL strUpdateSQL, False
  35.                     DBEngine.Idle dbRefreshCache
  36.  
  37.                 DoCmd.SetWarnings True 'on Warnings
  38.  
  39.                 'close/open forms
  40.                 oldpass.Value = ""
  41.                 newpass.Value = ""
  42.                 confirmpass.Value = ""
  43.                 DoCmd.OpenForm "frm_ChangePass", , , , , acHidden
  44.                 MsgBox "Password changed for " & strUser & " successfully.", vbOKOnly
  45.                 DoCmd.OpenForm "frm_Menu", acNormal, "", "", , acNormal
  46.  
  47.             Else
  48.  
  49.                 MsgBox "The password's do not match"
  50.                 Exit Sub
  51.  
  52.             End If 'end new confirm passwords If
  53.  
  54.         Else
  55.             MsgBox "Invalid Password. Please try again.", vbOKOnly, "Invalid Password"
  56.             oldpass.SetFocus
  57.  
  58.         End If 'end confirm old password If
  59.  
  60.     End If 'end confirm non null value's If
  61.  
  62. End Sub
Apr 12 '14 #12

zmbd
Expert Mod 5K+
P: 5,397
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.

(^_^)
Apr 13 '14 #13

NeoPa
Expert Mod 15k+
P: 31,769
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.
Apr 13 '14 #14

Brilstern
100+
P: 208
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.
Apr 16 '14 #15

NeoPa
Expert Mod 15k+
P: 31,769
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.
Apr 17 '14 #16

Brilstern
100+
P: 208
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.
Apr 23 '14 #17

NeoPa
Expert Mod 15k+
P: 31,769
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.
Apr 23 '14 #18

Brilstern
100+
P: 208
Understandable. :) I will look more into it when I have time to dive into it.
Apr 23 '14 #19

Post your reply

Sign in to post your reply or Sign up for a free account.