Extension of LogIn Table to display form based on user status

[JodiPhillips]

G'day,

I have a silly and simple problem that I need some guidance with.

Due to the way our network is set up, I am unable to use the group permissions for Access and have had to implement log in procedures via a log in table within the database. This works fine. I have now expanded this table to include further data about the authorised user – Power User, Team Leader, & Facilitator.

Depending on the user’s status as to which one of these they are (or as the default status as a regular user) I want to display a form specific for that user’s status, e.g. If the user is true in the log in table as a Power user, I want a “power user” form to be displayed. Sometimes we will have team leaders who are also facilitators so I need to check both Team Leader and Facilitator status - thus I have four situations (forms) to load up. 1. If Power user = true then load Power user form; 2. if team leader = true then load Team leader form; 3. if facilitator = true then load facilitator form; and 4. if both team leader and facilitator = true then load team facilitator form.

The log in table consists of:
lngEmpID – Autonumber (PK)
txtUserID – txt
txtpword - txt
txtfirstname – txt
Team Leader – Boolean (Y/N)
Facilitator – Boolean (Y/N)
Power – Boolean (Y/N)

I have the following forms:
frmLogOn
Start Facilitator
Start Team Facilitator
Start Power
Start Team Leader

This is the code I have that works fine for the log in part of the process:

Class

Option Compare Database
 
Public LoggedInUser As String
 
Public Function GetLoggedInUser() As String
GetLoggedInUser = LoggedInUser
End Function
 
Public Function SetLoggedInUser(sUserID As String)
LoggedInUser = sUserID
 
End Function

Module

Option Compare Database
 
Public LoggedInUser As String
 
Public Function GetLoggedInUser() As String
GetLoggedInUser = LoggedInUser
End Function
 
Public Function SetLoggedInUser(sUserID As String)
LoggedInUser = sUserID
 
End Function

****I wasn’t sure if the above needed to be in a class or a module so I created the same in each – could anyone shed some light on which I should have used – class or module?

Code for LogOn

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
 
 
Private Sub cmdLogOn_Click()
 
    'Set the variable for the password entry = sPswd
    Dim sPswd 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
 
 
    Call SetLoggedInUser(Me.cboUserID)
 
 
        'Close the LogOn form and open the Start form
        DoCmd.Close acForm, "frmLogOn", acSaveNo
        DoCmd.OpenForm ("Start Form CL&D")
 
 
 
End Sub

The last block from ‘Close LogOn form and open the start form is the part I want to change and have had several unsuccessful attempts. At this stage the code is only at the point of trying to get the power user (“Start Power”) form to load, the rest of the forms I will do once I get this worked out.

The amended code just doesn’t want to load the form – I’ve tried putting the Call frmLoader sub in at various places without success. I’ve also tried to Dim Power as both string and Boolean without success.

Amended Code

Option Compare Database
Dim intLogins As Integer
 Dim Power As Boolean
 
 
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
 
 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
 
 End Sub        
 
Private Sub cmdLogOn_Click()
 
    'Set the variable for the password entry = sPswd
    Dim sPswd 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
 
 Call FrmLoader
 
 
    Call SetLoggedInUser(Me.cboUserID)
 
 
 
End Sub

Can anyone tell me where I’m going wrong – I know this is simple but I’ve been at this for the last few hours and am well and truly frustrated =(

Thanks for any help - all help is greatly appreciated!

[nico5038]

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]

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.quit 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]

Place a breakpoint on the line with:

        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]

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:

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]

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]

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):

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
 
        Private Sub FrmLoader()
 
            If sStatus = P Then DoCmd.OpenForm ("frmStartPower") 
              Else: If sStatus = T Then DoCmd.OpenForm ("frmStartTeam") 
          Else: If sStatus = B Then DoCmd.OpenForm ("frmStartTeamFacilitator") 
              Else: If sStatus = F Then DoCmd.OpenForm ("frmStartFacilitator") 
              Else 
              DoCmd.OpenForm ("frmStartParticipant") 
 
             End If 
        End Sub
 
 
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
 
 
    Call SetLoggedInUser(Me.cboUserID)
 
      sStatus = DLookup("Status", "tblLogIn", "txtUserID= ' " & Me!cboUserID & " ' ")
         
 
 
     Call FrmLoader
         
 
     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]

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]

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]

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]

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):

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]

Thanks Jodi <blush> :-)

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
 

Success with your application !

Nic;o)

[JodiPhillips]

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]

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:

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;)