Close form after running SQL: Error 2585

[Brilstern]

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.

[zmbd]

For trouble shooting Comment out line 7 and 12
Insert after line 9

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.

[jimatqsi]

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

[Brilstern]

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.

[jimatqsi]

Maybe you need to open frm_Menu before closing frm-ChangePass (duh on all of us).

Jim

[Brilstern]

Unfortunately that doesn't change anything. Tried that a long time ago.

[Brilstern]

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

[jimatqsi]

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

[Brilstern]

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

[Brilstern]

Tried the public function method and no luck... Not really sure where to head from here.

[jimatqsi]

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

[Brilstern]

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]

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]

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.

[Brilstern]

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.