Unanswered: Password Protected Login Form w/user permisions
Hello All, I'm currently looking for some guidance on creating a user login form which will open a different switchboard depending on a value attached to the user's record in a table.
I have created a table called "tblAuthorisedUsers" which as 6 columns;
"ID" -Autonumber/primary key
"Name" - Full Name eg. Mike Smith
"Postion" - Job title
"Username" - eg. msmith
"Password" - *****
"AccessLevel" - "1" = admin user, "2" = general user
I am hoping to implement something that will obviously check the user name and password as usual and then open a different form depending on the value of "AccessLevel", I intend to make an admin switch board and a general user switchboard to give the option of altering user accounts and enabling the shift key.
The table is used elsewhere in the database, hence the "Name" & "Position" columns, if you feel this is an issue I can alter it quite easily without effecting anything too much!
I have quite a headache after working on this db all day and I was hoping someone will take pity on me and stear me in the right direction! I have done several searches but have yet to come across anything that does what I want (and I can understand!).
I am relatively new to Access, so please excuse me if I ask too many questions! (I'm using Access 2003 if that helps.)
Any help you can offer will be greatly appreciated!
Create a dialog form to prompt for the username and password. When the user clicks ok, it looks up in the table to see if the username/password combination is right. If the password is right, then act according to the AccessLevel and do whatever you want, open a specific "switchboard" or have your switchboard modify itself..... This all seems like I am just re-iterating what you want to achieve, but I don't know how much of a "steer" you need,... I guess it might get you started? Where have you gotten stuck?
You should also do a search around the place, there are some recent topics here which are all about Access security and there are some alternatives you may not have considered, such as the ability to pull the Windows Username from the system to determine security levels.
Depends on how you've setup your login form. Mine has a cboUsername and txtPassword controls on it, with an OK and a Cancel button. The OK button does this (psudocode):
'extract the user record from the USER table with the ID of cboUsername
'examine the txtPassword with the extracted USER's password
'if passwords match then set a global variable called vUserLevel to the extracted USER's UserLevel field and then open the menu screen
'if the passwords don't match, trash the txtPassword, set the focus there and show a message informing the user that the passwords don't match. End Sub
My menu screen uses the vUserLevel and configures itself accordingly.
Sorry, but I'd only use VBA for this, as opposed to using expressions with IIf.
I'm really asking for help with the complete solution including a simple Login based on the above, which will not allow a "null" entry in the password field?
Ok, so get started! Make a combo box for your usernames and a textbox for the password, an OK button and a Cancel button.
Right click the Cancel button and choose Build Event (Code Builder) and type DoCmd.Quit. There, that's done.
Now do the same with the OK button and enter this:
Dim rsUsers As Recordset
If Me.txtPassword = "" Then
MsgBox "Password cannot be empty."
Set rsUsers = CurrentDb.OpenRecordset("SELECT * FROM tblAuthorisedUsers WHERE ID = " & Me.cboUsername)
If Me.txtPassword = rsUsers!Password Then
AccessLevel = rsUsers!AccessLevel
DoCmd.Close , Me.Name
AccessLevel = rsUsers!AccessLevel = 0
MsgBox "Incorrect username or password"
Me.txtPassword = ""
Set rsUsers = Nothing
Now there's no error handling in that and it's all untested code, so don't shoot me if there's a minor bug in it.
Also, this would rely on a global variable called AccessLevel being declared.