Another R/T 2467 problem

[Maccus]

I am in need of some expertise.

I have have read through the R/T error 2467 threads but none seem to deal with my particular problem (apart from the suggestion that it could be a bug in Access 2010)

To keep it simple, I have created a Navigation form with the Login built into the header. For cosmetic reasons, I want the navigation menus hidden until the user has logged in. The code (that is adapted from Austin72406 (YouTube ref)) works up until the If Then Else that actually makes the menus visible. At that point, it puts up the R/T 2467 error and I just can't understand why.

I have run the make visible code from a separate cmd button in the header and it works fine. Integrate that into the cmdLogin_Click() sub and it fails. And I am banging my head against the wall trying to work out why. :banghead:

The code is below. Can someone spot the error, please, because I'm blowed if I can.

Dim SecurityID As Integer
Dim TempPass As String
Dim ID As Integer

If IsNull(Me.txtUserName) Then
    Msgbox "Please enter your UserName", vbInformation, "UserName Required"
    Me.txtUserName.SetFocus
ElseIf IsNull(Me.Password) Then
    Msgbox "Please enter your Password", vbInformation, "Password Required"
    Me.txtPassword.SetFocus
Else
    If (IsNull(DLookup("[UserName]", "tblUser", "[UserName] = '" & 
         Me.txtUserName.Value & "' And Password = '" & Me.txtUserName.Value & 
         "'"))) Then
    Msgbox "Incorrect UserName or Password"
Else
    SecurityID = Dlookup("SecurityID", "tblUser", "UserName = '" & 
         Me.txtUserName.Value & "'")
    TempPass = Dlookup("Password", "tblUser", "UserName = '" & 
         Me.txtUserName.Value & "'")
    ID = Dlookup("UserID", "tblUser", "UserName = '" & 
         Me.txtUserName.Value & "'")
    DoCmd.Close

    If (TempPass = "Password") Then
        Msgbox "Please change your Password.", vbInformation, "New Password Required!"
        DoCmd.OpenForm "User Updater",,,"[UserID] = " & ID
    Else
        'R/T error 2467 starts here
        If SecurityID = 1 Then  '1=Admin, 2=User
           Me!NavigationControl05.Visible = True

There are more lines of VBA but the code falters at that line and no matter what alternative I use (I have created this form twice which makes me think I have a problem elsewhere) I cannot crack it.

All help is greatly appreciated.

 

[isladogs]

Its hard to tell without seeing all the code, but ...
...from the bit I can see, the line after Me.Password.SetFocus should be ElseIf
There MAY be other issues as well??

...
 Me.txtPassword.SetFocus
[COLOR="Red"]ElseIf[/COLOR]
    If
...

 

[Maccus]

Thanks Ridders, I'll check my works laptop as I use a Mac at home and may have copied the code over incorrectly. If I haven't I'll see if that has any impact.

Update: ElseIf causes a compile error. Else works.

 

[The_Doc_Man]

My question is, where is this code triggered? HOW is it triggered when it fails? You have already pointed out that if you run it manually from a click event, it works OK. So... where is it running when it DOESN'T run OK? More specifically, from what event?

 

[arnelgp]

if the code is run in the Navigation Form
on out of it's subform:

Me.Parent!NavigationControl05.Visible = False

Or Me.Parent!NavigationControl05.Visible = Not (Me.Parent!NagivationControl05.Visible)

 

[Maccus]

Hi Doc Man, my apologies. The click event fails. What I was trying to say was that the code works ok until the If SecurityID is reached. At that line in the code, it breaks.

I should have added that I set the visible property of the menus in the Navigation Form on the OnLoad event but did make the UserName input box the SetFocus. I'm starting to wonder if my problem stems from that somehow.

 

[Maccus]

Thanks Arnelgp, I'll check out your correction and see if it makes a difference.����

 

[The_Doc_Man]

OK, your indentation is off. The Else (and the rest of the code I show here) is at a deeper level of nesting than the code on the other side of this ELSE. Which is probably why you got a suggestion regarding ElseIf. But look at the three highlighted lines below.

Else
[COLOR="Red"]    SecurityID = Dlookup("SecurityID", "tblUser", "UserName = '" & 
         Me.txtUserName.Value & "'")
[/COLOR]    TempPass = Dlookup("Password", "tblUser", "UserName = '" & 
         Me.txtUserName.Value & "'")
    ID = Dlookup("UserID", "tblUser", "UserName = '" & 
         Me.txtUserName.Value & "'")
[COLOR="SeaGreen"]    DoCmd.Close[/COLOR]

    If (TempPass = "Password") Then
        Msgbox "Please change your Password.", vbInformation, "New Password Required!"
        DoCmd.OpenForm "User Updater",,,"[UserID] = " & ID
    Else
        'R/T error 2467 starts here
[COLOR="Blue"]        If SecurityID = 1 Then  '1=Admin, 2=User
           Me!NavigationControl05.Visible = True[/COLOR]

The things I highlighted may be relevant. Normally, one would say that the line in red would define SecurityID inside that Else branch. But what in the world is that green DoCmd.Close actually closing?

If I recall the order of events correctly, whatever is closing is going to go through ALL of its events before returning to the event triggering the closure sequence. Which means that whatever was closing IS closed by the time you get to the blue (offending) line.

If my guess is correct, the DoCmd.Close is closing "Me" - which means you have de-instantiated the variables and controls you were working with. But the code can't "go away" yet because it is still kept alive by the reference on the call stack. So in essence you have done a RESET on that form. Now, I COULD be wrong here - but it is suspicious.

To be fair, I've done this sort of thing before - closing a form as the result of a test or condition in its own code - but I've always assured that it was the last thing I tried to execute in the sequence. I.e. at the last line of the event code just before the Exit Sub or End Sub. That way when the code referenced on the stack as the Return target is called, it doesn't attempt to touch anything else of significance - 'cause it ain't there no more!

 

[arnelgp]

if it is a simple unbalance If... Else,
it will not be a RT, it will be
compiler error, If End If block.

remember that this is just a snippet
of the entire code.

 

[Maccus]

Hi Doc Man. The 'DoCmd.Close is a legacy from when I had a separate login page that I just hadn't spotted it when I transferred the code to the Header of the Navigation Form. Commenting it out, however, hasn't overcome the failure of the code when it gets to the If SecurityID line.

To settle the issues about my indenting, here is the code as it is in my db (I've set up my account on my works laptop now) :

Private Sub cmdLogin_Click()

 
Dim SecurityID As Integer
Dim TempPass As String
Dim ID As Integer
'**********************************************************************************************
'Checks whether a UserName or Password has been inserted and provides msgbboxes if they haven't
'**********************************************************************************************
If IsNull(Me.txtUserName) Then
    MsgBox "Please enter your UserName", vbInformation, "UserName Required"
    Me.txtUserName.SetFocus
ElseIf IsNull(Me.txtPassword) Then
    MsgBox "Please enter your Password", vbInformation, "Password Required"
    Me.txtPassword.SetFocus
Else
   
    '*********************************************************************************************
    'Checks whether a correct UserName or Password has been inserted and provides msgbboxes if not
    '*********************************************************************************************
    
    If (IsNull(DLookup("[UserName]", "tblUser", "[UserName] = '" & Me.txtUserName.Value & "' And Password = '" & Me.txtPassword.Value & "'"))) Then
        MsgBox "Incorrect UserName or Password"
    Else
        
        '******************************************************************************************
        'Checks whether a Password is temporary or not and redirects to an update User details form
        '******************************************************************************************
        
        SecurityID = DLookup("SecurityID", "tblUser", "UserName = '" & Me.txtUserName.Value & "'")
        TempPass = DLookup("Password", "tblUser", "UserName = '" & Me.txtUserName.Value & "'")
        ID = DLookup("UserID", "tblUser", "UserName = '" & Me.txtUserName.Value & "'")
        'DoCmd.Close
        
        If (TempPass = "Password") Then
            MsgBox "Please change your password.", vbInformation, "New Password Required!"
            DoCmd.OpenForm "User Updater", , , "[UserID] = " & ID
        Else
            
            '******************************************************
            'Checks the security level before opening the Main Menu
            'Level 1 is Admin, Level 2 is User, Level 3 is Guest
            '******************************************************
            
            If SecurityID = 1 Then
                Me.Parent!NavigationControl0.Visible = True
                Me.Parent!NavigationControl5.Visible = True
            Else
                Me.Parent!NavigationControl0.Visible = True
                Me.Parent!NavigationControl5.Visible = True
                
            End If
            
        End If
        
    End If
    
End If
End Sub

 

[The_Doc_Man]

In my experience, there is no substitute for single-stepping your way through the code with the "Locals" window open to show you what is in context. That code snippet is short enough that you should be able to put a breakpoint at the opening "If" and open that Locals window. Navigate the Locals display to show you what is and is not instantiated. Then, once you are satisfied that all is as it should be, step through to the point where this error occurs. BUT... if in fact something closes or otherwise goes out of scope, the Locals display will change pretty quickly.

If this were a humongous loop, stepping through it would be a pain in the toches but for code that is essentially just a single pass through a few branches, it should be eminently possible to step through it. And it would pinpoint the step that actually causes the problem. That is what you need.

Further, if your guess regarding your entry-point conditions is wrong, using the Locals window (on the code "View" drop-down) will verify whether you are, as we say down in south Louisiana, "barking up the wrong tree."

 

[Minty]

I'm not a great fan of Else ElseIf's .

I tend to simply do an

If Then Condition Fails
     MsgBox"It Failed"
     Exit sub
End If

It makes things so much easier to read - probably because I'm pretty simple.