473,289 Members | 1,791 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

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
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
13 3197
nico5038
3,080 Expert 2GB
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
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
3,080 Expert 2GB
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
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
3,080 Expert 2GB
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
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
3,080 Expert 2GB
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
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
3,080 Expert 2GB
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
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
3,080 Expert 2GB
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
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
3,080 Expert 2GB
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

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

Similar topics

2
by: harris | last post by:
I am needing some help with a few asp pages and a login script. Let me start by describing my project. I am trying to connect to a database and verify a users login and password, then based on...
2
by: tractng | last post by:
Guys, I really need help with this. I need to creat a login page for the website. Its an existing site that connects to SQL 7 with a local user in the database called 'maya'. It was...
2
by: Shakun | last post by:
Hi All, This is my 1st posting to this group. Can any1 help me with the "Remember Me" which is there in a login form. Im pasting the code below. Im not able to set a cookie.. Thanks, Shakun...
1
by: Friends | last post by:
Hi I need to set security for row level but not based on Database user's login. It should be based on the user table login. For the particular user I need to allow only the particular records to...
1
by: pj | last post by:
I'm trying to redirect users to another page after they Authenticate with the ASP.NET login controls. The user is able to login, but I can't get the response.redirect to work. Can anyone help? ...
1
by: xcelmind | last post by:
Hello Dev. Guru, I want to at this time introduce myself. I am Stanley Ojadovwa by name. I’m a freelance and a newbie in web application development. I’m currently using ASP as my application...
3
by: =?Utf-8?B?QmlsbHkgWmhhbmc=?= | last post by:
I want to limit the user only login the system one time at the same time. I don't want him login the system two with the same user at the same time. How to do this? If i have a table to record...
1
by: angelicdevil | last post by:
i have listbox 1 which displays status , based on selection of status listbox 2 displays usernames. and based on username selected the textbox displays the email id. its working fine till...
21
by: tvnaidu | last post by:
This is the Java script I am using fo rlogin page, but cursor not pointing to login box, any idea how can I point cursor to login box when this page loaded?. here admin login take to control page and...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.