By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,519 Members | 2,258 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,519 IT Pros & Developers. It's quick & easy.

Extension of LogIn Table to display form based on user status

JodiPhillips
P: 26
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
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Public LoggedInUser As String
  4.  
  5. Public Function GetLoggedInUser() As String
  6. GetLoggedInUser = LoggedInUser
  7. End Function
  8.  
  9. Public Function SetLoggedInUser(sUserID As String)
  10. LoggedInUser = sUserID
  11.  
  12. End Function
Module

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Public LoggedInUser As String
  4.  
  5. Public Function GetLoggedInUser() As String
  6. GetLoggedInUser = LoggedInUser
  7. End Function
  8.  
  9. Public Function SetLoggedInUser(sUserID As String)
  10. LoggedInUser = sUserID
  11.  
  12. 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

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Dim intLogins As Integer
  3.  
  4. Private Sub cboUserID_AfterUpdate()
  5.     'After selecting user name set focus to textbox
  6.     Forms!frmLogOn!txtPass.SetFocus
  7.  
  8. End Sub
  9.  
  10. Private Sub LogIn()
  11. 'Sets the number of attempts to log in at 3, and will kick user if exceeded
  12. intLogins = intLogins + 1
  13.  If intLogins > 2 Then
  14.         MsgBox "You are not authorised to access this database.", vbCritical, "Restricted Access!"
  15.         Application.Quit
  16.         End If
  17.         End Sub
  18.  
  19.  
  20. Private Sub cmdLogOn_Click()
  21.  
  22.     'Set the variable for the password entry = sPswd
  23.     Dim sPswd As String
  24.     'Count logins and step
  25.  
  26.  Call LogIn
  27.  
  28.         'User ID and Password cannot contain a null value
  29.         If IsNull(Me!cboUserID) = True Or IsNull(Me!txtPass) = True Then
  30.             MsgBox "please enter a valid userid and password"
  31.             Exit Sub
  32.         End If
  33.  
  34.     'Lookup the value of variable in table LogIn
  35.     sPswd = Nz(DLookup("txtpword", "tblLogIn", "txtUserID='" & Me!cboUserID & " ' "), "")
  36.  
  37.         'Check to see of passwords match
  38.         If Me!txtPass <> sPswd Then
  39.             MsgBox "Invalid UserID/Password", vbOKOnly, "Try Again"
  40.             Exit Sub
  41.         End If
  42.  
  43.  
  44.     Call SetLoggedInUser(Me.cboUserID)
  45.  
  46.  
  47.         'Close the LogOn form and open the Start form
  48.         DoCmd.Close acForm, "frmLogOn", acSaveNo
  49.         DoCmd.OpenForm ("Start Form CL&D")
  50.  
  51.  
  52.  
  53. 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
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Dim intLogins As Integer
  3. Dim Power As Boolean
  4.  
  5.  
  6. Private Sub cboUserID_AfterUpdate()
  7.     'After selecting user name set focus to textbox
  8.     Forms!frmLogOn!txtPass.SetFocus
  9.  
  10. End Sub
  11.  
  12. Private Sub LogIn()
  13. 'Sets the number of attempts to log in at 3, and will kick user if exceeded
  14. intLogins = intLogins + 1
  15.  If intLogins > 2 Then
  16.         MsgBox "You are not authorised to access this database.", vbCritical, "Restricted Access!"
  17.         'Application.Quit
  18.         End If
  19.         End Sub
  20.  
  21.  Private Sub FrmLoader()
  22.  
  23.  Power = DLookup("Power", "tblLogIn", "txtUserID=' " & Me!cboUserID & " ' ")
  24.  If Power = True Then DoCmd.Close acForm, "FrmLogOn", acSaveNo
  25.  DoCmd.OpenForm ("Start Power")
  26.  End If
  27.  
  28.  End Sub        
  29.  
  30. Private Sub cmdLogOn_Click()
  31.  
  32.     'Set the variable for the password entry = sPswd
  33.     Dim sPswd As String
  34.  
  35.         'Count logins and step
  36.  
  37.  Call LogIn
  38.  
  39.         'User ID and Password cannot contain a null value
  40.         If IsNull(Me!cboUserID) = True Or IsNull(Me!txtPass) = True Then
  41.             MsgBox "please enter a valid userid and password"
  42.             Exit Sub
  43.         End If
  44.  
  45.     'Lookup the value of variable in table LogIn
  46.     sPswd = Nz(DLookup("txtpword", "tblLogIn", "txtUserID='" & Me!cboUserID & " ' "), "")
  47.  
  48.         'Check to see of passwords match
  49.         If Me!txtPass <> sPswd Then
  50.             MsgBox "Invalid UserID/Password", vbOKOnly, "Try Again"
  51.             Exit Sub
  52.          End If
  53.  
  54.  Call FrmLoader
  55.  
  56.  
  57.     Call SetLoggedInUser(Me.cboUserID)
  58.  
  59.  
  60.  
  61. 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!
Oct 9 '07 #1
Share this Question
Share on Google+
13 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Oct 9 '07 #2

JodiPhillips
P: 26
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
Oct 10 '07 #3

nico5038
Expert 2.5K+
P: 3,072
Place a breakpoint on the line with:
Expand|Select|Wrap|Line Numbers
  1.         If IsNull(Me!cboUserID) = True Or IsNull(Me!txtPass) = True Then
  2.  
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)
Oct 10 '07 #4

JodiPhillips
P: 26
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:

Expand|Select|Wrap|Line Numbers
  1. Dim Power As Boolean
  2.  
  3.   Private Sub FrmLoader()
  4.  
  5.  Power = DLookup("Power", "tblLogIn", "txtUserID=' " & Me!cboUserID & " ' ")
  6.  If Power = True Then DoCmd.Close acForm, "FrmLogOn", acSaveNo
  7.  DoCmd.OpenForm ("Start Power")
  8.  End If
  9.  
  10.  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!
Oct 11 '07 #5

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 11 '07 #6

JodiPhillips
P: 26
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):

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Dim intLogins As Integer
  3.  
  4. Private Sub cboUserID_AfterUpdate()
  5.     'After selecting user name set focus to textbox
  6.     Forms!frmLogOn!txtPass.SetFocus
  7.  
  8. End Sub
  9.  
  10. Private Sub LogIn()
  11. 'Sets the number of attempts to log in at 3, and will kick user if exceeded
  12. intLogins = intLogins + 1
  13.  If intLogins > 2 Then
  14.         MsgBox "You are not authorised to access this database.", vbCritical, "Restricted Access!"
  15.         'Application.Quit
  16.         End If
  17.         End Sub
  18.  
  19.         Private Sub FrmLoader()
  20.  
  21.             If sStatus = P Then DoCmd.OpenForm ("frmStartPower") 
  22.               Else: If sStatus = T Then DoCmd.OpenForm ("frmStartTeam") 
  23.           Else: If sStatus = B Then DoCmd.OpenForm ("frmStartTeamFacilitator") 
  24.               Else: If sStatus = F Then DoCmd.OpenForm ("frmStartFacilitator") 
  25.               Else 
  26.               DoCmd.OpenForm ("frmStartParticipant") 
  27.  
  28.              End If 
  29.         End Sub
  30.  
  31.  
  32. Private Sub cmdLogOn_Click()
  33.  
  34.     'Set the variable for the password entry = sPswd
  35.     Dim sPswd As String
  36.     Dim sStatus As String 
  37.  
  38.         'Count logins and step
  39.  
  40.  Call LogIn
  41.  
  42.         'User ID and Password cannot contain a null value
  43.         If IsNull(Me!cboUserID) = True Or IsNull(Me!txtPass) = True Then
  44.             MsgBox "please enter a valid userid and password"
  45.             Exit Sub
  46.         End If
  47.  
  48.     'Lookup the value of variable in table LogIn
  49.     sPswd = Nz(DLookup("txtpword", "tblLogIn", "txtUserID='" & Me!cboUserID & " ' "), "")
  50.  
  51.         'Check to see of passwords match
  52.         If Me!txtPass <> sPswd Then
  53.             MsgBox "Invalid UserID/Password", vbOKOnly, "Try Again"
  54.             Exit Sub
  55.          End If
  56.  
  57.  
  58.     Call SetLoggedInUser(Me.cboUserID)
  59.  
  60.       sStatus = DLookup("Status", "tblLogIn", "txtUserID= ' " & Me!cboUserID & " ' ")
  61.          
  62.  
  63.  
  64.      Call FrmLoader
  65.          
  66.  
  67.      DoCmd.Close acForm, "frmLogOn", acSaveNo   
  68.  
  69. 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
Oct 11 '07 #7

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 11 '07 #8

JodiPhillips
P: 26
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.
Oct 12 '07 #9

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 12 '07 #10

JodiPhillips
P: 26
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):

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdLogOn_Click()
  2.  
  3.     'Set the variable for the password entry = sPswd
  4.     Dim sPswd As String
  5.     Dim sStatus As String
  6.  
  7.  
  8.       'Count logins and step
  9.  
  10.  Call LogIn
  11.  
  12.         'User ID and Password cannot contain a null value
  13.         If IsNull(Me!cboUserID) = True Or IsNull(Me!txtPass) = True Then
  14.             MsgBox "please enter a valid userid and password"
  15.             Exit Sub
  16.         End If
  17.  
  18.     'Lookup the value of variable in table LogIn
  19.     sPswd = Nz(DLookup("txtpword", "tblLogIn", "txtUserID='" & Me!cboUserID & " ' "), "")
  20.  
  21.         'Check to see of passwords match
  22.         If Me!txtPass <> sPswd Then
  23.             MsgBox "Invalid UserID/Password", vbOKOnly, "Try Again"
  24.             Exit Sub
  25.          End If
  26.  
  27.         'Sets the loggedinuser for use in SQL statements
  28.         Call SetLoggedInUser(Me.cboUserID)
  29.  
  30.         'Lookup the persons status in the log in table
  31.         sStatus = DLookup("txtStatus", "tblLogIn", "txtUserID='" & Me!cboUserID & " ' ")
  32.         Debug.Print sStatus
  33.  
  34.  
  35.         'Selects the correct form to load based on the status
  36.         If sStatus = "P" Then DoCmd.OpenForm ("frmStartPower")
  37.              If sStatus = "L" Then DoCmd.OpenForm ("frmStartTeam")
  38.              If sStatus = "F" Then DoCmd.OpenForm ("frmStartFacilitator")
  39.              If sStatus = "B" Then DoCmd.OpenForm ("frmStartTeamFacilitator")
  40.              If sStatus = "A" Then DoCmd.OpenForm ("frmStartParticipant")
  41.              If sStatus = "S" Then DoCmd.OpenForm ("frmSecondment")
  42.  
  43.            'Close the log on form
  44.             DoCmd.Close acForm, "frmLogOn", acSaveNo
  45.  
  46.  
  47. 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
Oct 24 '07 #11

nico5038
Expert 2.5K+
P: 3,072
Thanks Jodi <blush> :-)
Expand|Select|Wrap|Line Numbers
  1. BTW for the multiple IF's you can use:
  2. IF ..
  3. ELSEIF ...
  4. ELSEIF ...
  5. ELSEIF ...
  6. endif
  7.  
  8. ' or
  9.  
  10. Select case field
  11. Case "F"
  12.  docmd.openform "F"
  13. Case "X"
  14.  docmd.openform "X"
  15. ...
  16. end select
  17.  
Success with your application !

Nic;o)
Oct 24 '07 #12

JodiPhillips
P: 26
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
Oct 25 '07 #13

nico5038
Expert 2.5K+
P: 3,072
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:
Expand|Select|Wrap|Line Numbers
  1. Docmd.Openform DLookup("txtFormname", "tblLogIn", "txtUserID='" & Me!cboUserID & " '  and txtStatus")
  2.  
You could also use another field, but I guess you get the idea...
I know I'm lazy <LOL>

Nic;)
Oct 25 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.