Extension of LogIn Table to display form based on user status

**nico5038** · Oct 9 '07, 08:57 PM

Not yet sure what's causing the trouble, but would like to ask you to start with removing the class. Having two similar named functions can confuse Access.
Also make sure that the modulename isn't the same as the function.

Then try again and let me know where it goes wrong.
Best to place a breakpoint (click the left rules so a dot appears) and trigger the code. When the breakpoint is hit use the F8 key to single step through the code to see what happens and use the cursor above fields to see their value.

Nic;o)

**JodiPhillips** · Oct 10 '07, 12:21 AM

G'day Nic!

Thanks for your reply.

I've removed that class module =) thanks for that.

I set breakpoints at various places, however as I have to actually run the form to trigger the code (I can't seem to trigger from within the VB window - possibly because its running from the user input?) when I flick back to the code I can't actually see what its doing.

I set watches on the both cboUserID and Power variables and this shows "<out of context>" for both; and "Empty" for the data types. Is this much help at all?

What happens with the form when I run it is, it will kick me (try to that is - I've commented out the application.qui t command) which corresponds with the LogIn Sub.

With the power variable declared as a boolean, will this affect my Dlookup - the Power field in the log in table is a boolean, am I right to declare this variable with that data type?

Does the syntax for the frmLoader sub look okay? I want to know at this point if the value in the Power field is true or false and if true load the Start power form. Have I put the Call frmLoader in the wrong place?

Thanks again =)

Jodi

**nico5038** · Oct 10 '07, 07:21 AM

Place a breakpoint on the line with:

Code:

        If IsNull(Me!cboUserID) = True Or IsNull(Me!txtPass) = True Then

and press the cmdLogOn button.
Then use F8 for the single stepping and inspection of the variables.
Keep in mind that only after an assign (=) statement the variable will be filled.

Nic;o)

**JodiPhillips** · Oct 11 '07, 01:55 AM

G'day again Nic,

Firstly thank you very much for your time - it is greatly appreciated.

I set the breakpoint as per your last post. Pressing the cmdLogOn button and switching back to the VB window still doesn't show any changes in either the watched variables or the immediates.

The original code for LogOn works fine - it loads up the default form without a problem. As soon as I add these lines (lines 3, 21 -26, & 54 as above in the amended code block) however:

Code:

Dim Power As Boolean
 
  Private Sub FrmLoader()
 
 Power = DLookup("Power", "tblLogIn", "txtUserID=' " & Me!cboUserID & " ' ")
 If Power = True Then DoCmd.Close acForm, "FrmLogOn", acSaveNo
 DoCmd.OpenForm ("Start Power")
 End If
          
 Call FrmLoader

things go haywire. Once these lines are added, (even with the breakpoint and pressing the cmdLogOn button I cannot step through with F8) nothing happens except the message box, and there is no change in the watched variables. After three presses of the cmdLogOn button the message box pops up to say "You are not authorised to access this database", it executes this part of the LogIn Sub but nothing further. (I've checked the VB window after each time I press the button).

Thanks again Nic!

**nico5038** · Oct 11 '07, 10:41 AM

In line 6-7 above you're cutting off the branch your sitting on....
You'll need to open the new form first, before you can close the one with the code.

Nic;o)

**JodiPhillips** · Oct 11 '07, 10:56 PM

Hi again Nic =).

Thanks for that. I've now changed around the open and close, however I'm still having the same problem as I h had before. It doesnt seem to want to execute the frmLoader sub no matter where in the LogOn sub I put the call. What am I doing wrong? Here is my latest coding attempt (I've bolded the changes):

Code:

Option Compare Database
Dim intLogins As Integer

Private Sub cboUserID_AfterUpdate()
    'After selecting user name set focus to textbox
    Forms!frmLogOn!txtPass.SetFocus

End Sub

Private Sub LogIn()
'Sets the number of attempts to log in at 3, and will kick user if exceeded
intLogins = intLogins + 1
 If intLogins > 2 Then
        MsgBox "You are not authorised to access this database.", vbCritical, "Restricted Access!"
        'Application.Quit
        End If
        End Sub
        
        [B][I]Private Sub FrmLoader()[/I][/b][I][/i]
        
          [B][I]  If sStatus = P Then DoCmd.OpenForm ("frmStartPower")[/I][/B][I][/I] 
          [B][I]    Else: If sStatus = T Then DoCmd.OpenForm ("frmStartTeam")[/I][/B][I][/I] 
          [B][I]Else: If sStatus = B Then DoCmd.OpenForm ("frmStartTeamFacilitator")[/I][/B][I][/I] 
           [B][I]   Else: If sStatus = F Then DoCmd.OpenForm ("frmStartFacilitator")[/I][/B][I][/I] 
         [B][I]     Else[/I][/B][I][/I] 
            [B][I]  DoCmd.OpenForm ("frmStartParticipant")[/I][/B][I][/I] 
            
             [B][I]End If[/I][/B][I][/I] 
        End Sub
        
      
Private Sub cmdLogOn_Click()

    'Set the variable for the password entry = sPswd
    Dim sPswd As String
   [B] [I]Dim sStatus As String[/I][/B] 
            
        'Count logins and step
        
 Call LogIn

        'User ID and Password cannot contain a null value
        If IsNull(Me!cboUserID) = True Or IsNull(Me!txtPass) = True Then
            MsgBox "please enter a valid userid and password"
            Exit Sub
        End If
    
    'Lookup the value of variable in table LogIn
    sPswd = Nz(DLookup("txtpword", "tblLogIn", "txtUserID='" & Me!cboUserID & " ' "), "")
    
        'Check to see of passwords match
        If Me!txtPass <> sPswd Then
            MsgBox "Invalid UserID/Password", vbOKOnly, "Try Again"
            Exit Sub
         End If
  
      
    Call SetLoggedInUser(Me.cboUserID)
 
      [B][I]sStatus = DLookup("Status", "tblLogIn", "txtUserID= ' " & Me!cboUserID & " ' ")[/I]
    [/B]     

    
    [B][I] Call FrmLoader[/I]
    [/B]     
  
     DoCmd.Close acForm, "frmLogOn", acSaveNo   
 
End Sub

Please forgive the lack of 'comment lines in the changed code - heh I tend to add these when my code will actually work. From the little I know this should work but it doesn't so I've obviously got a problem either in the syntax or position of the call or the declaration of the variable. Same deal as before even though I can trigger from outside the VB window, even adding breakpoints there is still no value changes in the watched window. Could this be because of the Me! property? Haha I used to be blonde, now I'm just bald!! <grin>

Thanks again

Jodi

**nico5038** · Oct 11 '07, 11:59 PM

Hmm, don't you have accidently also a form called frmLoader ?
I always use sub or fnc as prefix for sus or functions...

Otherwise place a breakpoint a the start of the code and use F8 for single stepping through the code to see why the sub isn't triggered.

Nic;o)

**JodiPhillips** · Oct 12 '07, 03:59 AM

Thanks for your reply Nic.

I've now changed the sub to a function and renamed frmLoader (gah cant believe I did that) as FormLoader.

At this stage I have not been able to F8 through the code no matter where I break - I've tried to do this over and over, and don't understand why it wont step. This has me stumped. F8 works fine on any other code I have - just not on this particular class object. Code in Excel will F8, code in other Access projects will F8 just not this one =(.

As I mentioned the code will run fine before I add in which form to choose. If I remove this block and return the code to the original state it works fine. As soon as I add the code in it wont trigger this block so I'm assuming it has to be an issue with where its nested. I've tried adding the sub/fnc call at various places (and yes without the F8 functionality its extremely difficult to ascertain where the problem is) but have been unsuccessful.

If I add the call before I call the LogIn sub it continues to allow the user to keep pressing the button with no result. If I place it after the LogIn sub but before the call for SetLoggedInUser fnc it will only execute to the LogIn sub and then terminate. If I place it after the SetLoggedInUser fnc call it still provides the same result (ie terminate after the dialogue box pops up to say you are not authorised).

Throughout all of this the watches set on the following expressions provides the following values which don't change:

sStatus: <can't compile the module>
intLogIns: <out of context>
cboUserID: <out of context>
sPswd: <out of context>
txtPass: <out of context>

Debug.Print sStatus shows me the correct result for whats in the table.

Am I just really dumb, is that why I'm not getting this? I'm a bit overwhelmed by this - I made a "pretty" excel worksheet with VB and my boss now expects me to be able to make his access dB just as "pretty" and has given me until 8 November to do it (I feel like quitting but can't I need the job to pay for my med science studies!)

Anyway Nic =) thanks bunches for your time.

**nico5038** · Oct 12 '07, 09:35 AM

Your sStatus is defined within a sub and not available when performing the sub for starting the right form.
To share the value the Dim statement needs to be before all subs...

F8 should worh when you have a breakpoint placed in code that's executed. When the code doesn't halt, you'll need to place it where you're sure it's executed.

Personally I would just code the form start in the sub itself and not create a new sub for just that, as it's only needed at one point.

Nic;o)

**JodiPhillips** · Oct 24 '07, 10:49 PM

Hello again Nic!

Sorry for the long delay in replying to your last post.

I finally figured out what was going wrong with at least some parts of my problem (still working through the reasons why F8 wont step through the code on my home PC).

Thanks for the tip on where to declare that variable and also the tip of not running with a sub.

This is the final effort (posting this in case its useful for someone else):

Code:

Private Sub cmdLogOn_Click()

    'Set the variable for the password entry = sPswd
    Dim sPswd As String
    Dim sStatus As String
    
   
      'Count logins and step
    
 Call LogIn
 
        'User ID and Password cannot contain a null value
        If IsNull(Me!cboUserID) = True Or IsNull(Me!txtPass) = True Then
            MsgBox "please enter a valid userid and password"
            Exit Sub
        End If

    'Lookup the value of variable in table LogIn
    sPswd = Nz(DLookup("txtpword", "tblLogIn", "txtUserID='" & Me!cboUserID & " ' "), "")
    
        'Check to see of passwords match
        If Me!txtPass <> sPswd Then
            MsgBox "Invalid UserID/Password", vbOKOnly, "Try Again"
            Exit Sub
         End If
         
        'Sets the loggedinuser for use in SQL statements
        Call SetLoggedInUser(Me.cboUserID)
        
        'Lookup the persons status in the log in table
        sStatus = DLookup("txtStatus", "tblLogIn", "txtUserID='" & Me!cboUserID & " ' ")
        Debug.Print sStatus
   
   
        'Selects the correct form to load based on the status
        If sStatus = "P" Then DoCmd.OpenForm ("frmStartPower")
             If sStatus = "L" Then DoCmd.OpenForm ("frmStartTeam")
             If sStatus = "F" Then DoCmd.OpenForm ("frmStartFacilitator")
             If sStatus = "B" Then DoCmd.OpenForm ("frmStartTeamFacilitator")
             If sStatus = "A" Then DoCmd.OpenForm ("frmStartParticipant")
             If sStatus = "S" Then DoCmd.OpenForm ("frmSecondment")
            
           'Close the log on form
            DoCmd.Close acForm, "frmLogOn", acSaveNo
    
            
End Sub

This works as intended (but will stop executing before the Call Log In sub if "end if" is inserted at any point after the first "If" statement when deciding which form to load, so for the sake of usefulness "end if's" have been omitted.)

Many, many thanks for your patience, your help, and most of all your time.

<hugs>

Jodi

**nico5038** · Oct 24 '07, 11:16 PM

Thanks Jodi <blush> :-)
[code=vb]
BTW for the multiple IF's you can use:
IF ..
ELSEIF ...
ELSEIF ...
ELSEIF ...
endif

' or

Select case field
Case "F"
docmd.openform "F"
Case "X"
docmd.openform "X"
...
end select
[/code]

Success with your application !

Nic;o)

**JodiPhillips** · Oct 25 '07, 12:00 AM

Heh! Lovely shade of vbRed!

I tried both "else:" and "select" and neither would execute as intended. It is really weird, everything stopped as soon as I introduced a second Else statement or a second Case, it drove me crazy! In the end I just went back to a basic "if" which is the only way the wretched thing would work (sans "end if"). The only thing I can think of that may be interfering is the called LogIn sub, I just don't know. The main thing is it works - as unelegant as the code is LOL!

Thanks for your kind wishes Nic, and again for your help =)

Jodi

**nico5038** · Oct 25 '07, 07:47 AM

OK, then one final solution making the IF's obsolete:
Change the txtStatus field cotants into holding the needed formname instead of the status and use the Dlookup() like:

Code:

Docmd.Openform DLookup("txtFormname", "tblLogIn", "txtUserID='" & Me!cboUserID & " '  and txtStatus")

You could also use another field, but I guess you get the idea...
I know I'm lazy <LOL>

Nic;)

Extension of LogIn Table to display form based on user status

Extension of LogIn Table to display form based on user status

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment