0

I have a database with a login form, and upon a successful login the main navigation form is supposed to open and the login form is supposed to close... except that the login form refuses to close itself and instead throws "Run-time error '2585': This action can't be carried out while processing a form or report event."

Here's what I've got for the code:

Private Sub buttonLogin_Click()
Dim hash As New CMD5
Dim salt As String
Dim result As String
Dim rs As DAO.Recordset
Dim rc As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblCurrentUser", dbOpenDynaset)
Set rc = CurrentDb.OpenRecordset("tblCustom", dbOpenDynaset)
'----Check If User ID Or Password Is Null----
If IsNull(ID) Then
    MsgBox "Please enter your User ID", vbOKOnly
    Me.ID.SetFocus
    Exit Sub
End If
If IsNull(Password) Then
    MsgBox "Please enter your password", vbOKOnly
    Me.Password.SetFocus
    Exit Sub
End If
'----Validate Login Information----
' This section builds the salted MD5 hash and compares
' it to the password stored in the personnel table.
result = DLookup("Password", "tblPersonnel", "EmpID = '" & ID & "'")
salt = ID & "-" & Password
If result <> hash.MD5(salt) Then
    MsgBox "Please enter a valid User ID and Password", vbOKOnly
    Me.ID.SetFocus
    Exit Sub
End If
'----Check User Access Permission----
If DLookup("AccessLevel", "tblPersonnel", "EmpID = '" & ID & "'") = 0 Then
    MsgBox "Access denied, please contact " & rc![DBA] & " for database access.", vbExclamation
    Me.ID.SetFocus
    Exit Sub
End If
'----Store Current User and Access Level to temp table----
rs.Edit
rs![UserID] = ID
rs![AccessLevel] = DLookup("AccessLevel", "tblPersonnel", "EmpID = '" & ID & "'")
rs.Update
rs.Close
rc.Close
'----Open Navigation Form, close Login----
DoCmd.OpenForm "frmNavMain", acNormal
DoCmd.Close acForm, "frmLogin"
End Sub

So as you can see I've got a lot going on when the user logs in. Any invalid entries are caught and the sub is exited if that happens, but assuming everything checks out, the sub just rolls down to the end there and opens the nav form and then it should close itself. The debugger points specifically at the DoCmd.Close acForm, "frmLogin" line at the bottom every time. I've tried moving that line into frmNavMain Form_OnLoad() but it still throws the same run-time error.

What am I missing here?

Radio Doc
  • 379
  • 1
  • 4
  • 18
  • Does frmNavMain open normally? –  Mar 28 '15 at 15:23
  • Yes frmNavMain opens without issue. frmLogin is the only form I have that will "auto close" as all of the other forms are viewed within frmNavMain. – Radio Doc Mar 28 '15 at 15:27

2 Answers2

1

Move

DoCmd.Close acForm, "frmLogin"

to the OnActivate event of frmNavMain. Also, check for code running in the OnClose even of frmLogin.

Gustav
  • 53,498
  • 7
  • 29
  • 55
  • That didn't work. I've got nothing in `Form_Close()` in frmLogin, and moving the `DoCmd.Close acForm, "frmLogin"` to frmNavMain `Form_Activate()` still kicked off the same run-time error. – Radio Doc Mar 28 '15 at 16:30
  • It has to work, so something else is going on. Try removing all other code from the click event and also opening of the navigation form. – Gustav Mar 28 '15 at 21:28
  • Well, something else is definitely going on, because just as a troubleshooting measure I created a new form called test with a single button on it: `Option Compare Database` `Private Sub Command0_Click()` `DoCmd.OpenForm "frmNavMain", acNormal` `DoCmd.Close acForm, "test"` The test form opens the nav form and closes itself without issue, so somewhere in my script there I left something open... – Radio Doc Mar 29 '15 at 06:20
  • OK, then clean up the offending forms, then put back one task at time until fail, then you have it. There are no other methods - other than, of course as Parfait mentions, to implement good coding practice. – Gustav Mar 29 '15 at 07:59
  • I'm at a complete loss for words at this point - I stripped out _everything_ in frmLogin except for the `DoCmd` for the opening and closing the forms, and it's still throwing that blasted run-time error. As a possibly related side note, I also noticed that the form wasn't responding to the `buttonLogin_Click()` sub and was instead going to the `buttonLogin_Enter()` sub (it's identical to the code for OnClick above). Perhaps there's some corruption in the form that isn't getting fixed by the compact and repair tool? – Radio Doc Mar 29 '15 at 08:55
  • 1
    You would normally not run any action code in the OnEnter event, indeed not code identical to that in the OnClick event. Remove the OnEnter code. – Gustav Mar 29 '15 at 15:43
  • Well... that's certainly interesting. Removing that code appears to have fixed the issue, but then the question comes to how would I enable being able to press enter to log in. I know most users (myself included) will want to go "User ID" -> [tab] / [enter] -> "Password" -> [enter] to login instead of having the click the button. I'm guessing moving the login script to the OnEnter portion of the password field would do the trick? – Radio Doc Mar 29 '15 at 15:55
  • No. That is done by setting the button's property Default to True. – Gustav Mar 29 '15 at 17:15
0

Try uninitializing your recordsets after you close them (always a good practice on all set objects as you free up cpu resources). See SO post. This even suggests Compact & Repair may help you as resources may have piled up in memory: 

Set rs = nothing 
Set rc = nothing

Another suggestion for the temp table update is to use an update action stored query called by DoCmd.OpenQuery or VBA query with DoCmd.RunSQL or CurrentDB.Execute and not the recordset approach.

Community
  • 1
  • 1
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • I tried setting the recordsets to nothing yet I'm still getting the run-time error. I'll try changing the temp table to a query and see if that helps. – Radio Doc Mar 29 '15 at 06:38
  • After code adjustments, try decompiling and compacting your database. See [instructions here](http://stackoverflow.com/questions/3266542/how-does-one-decompile-and-recompile-a-database-application). – Parfait Mar 29 '15 at 15:57