Diffrernt access level privileges

[Emmanuel]

I have a table with name tbl_login with fields:

UserID
FirstName
LastName
UserName
Password


I also have a form with name frm_login with textboxes:

One for Username with textbox name : txt_username

the other for passowrd with textbox name : txt_password


i have a command button with name cmd_login with the following codes behind it.



Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

If Trim(Me.txt_username.Value & vbNullString) = vbNullString Then
MsgBox prompt:="Username should not be left blank.", buttons:=vbInformation, title:="Username Required"
Me.txt_username.SetFocus
Exit Sub
End If

If Trim(Me.txt_password.Value & vbNullString) = vbNullString Then
MsgBox prompt:="Password should not be left blank.", buttons:=vbInformation, title:="Password Required"
Me.txt_password.SetFocus
Exit Sub
End If

'query to check if login details are correct'
strSQL = "SELECT FirstName FROM tbl_login WHERE Username = """ & Me.txt_username.Value & """ AND Password = """ & Me.txt_password.Value & """"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
If rst.EOF Then
MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, title:="Login Error"
Me.txt_username.SetFocus
Else


MsgBox prompt:="Hello, " & rst.Fields(0).Value & ".", buttons:=vbOKOnly, title:="SOFTWARE"
DoCmd.OpenForm "A"



I will like to create a new field in tbl_login with the name Access_level which will contain the following user access permissions;

Administrator
Accounts
Secretary


I will like my code to be re modified such that:

when login with access_level administrator, it should open form A
when login with access_level Accounts, it should open form B
when login with access_level Secretary, it should open form C


Any help with this will be greatly appreciated

[ranman256]

Along with userid and password is the field Level. (or call it whatever)

when user opens the db, the main menu form will open and grab the userID.
Then lookup that persons rights in order to enable/disable controls.
put the UserId into an invisible text box on the form,txtUserID , so queries can see it.

public gvUserID 


sub form_load()
dim vLevel


 gvUserID =  Environ("Username")      'get userID,visible in all forms


'get level from user table
   vLevel = Dlookup("[Level]","tUsers","[userID]='" & gvUserID & "'")


'now, enable/disable items on form
   select case vLevel
         case "A"  'admin 
             'all is enabled


         case "U"  'normal user
             txtBox1.enabled = false
             txtManager.enabled = false


         case "M"  'manager
             txtBox1.enabled = false
   end select
end sub

[Emmanuel]

Along with userid and password is the field Level. (or call it whatever)

when user opens the db, the main menu form will open and grab the userID.
Then lookup that persons rights in order to enable/disable controls.
put the UserId into an invisible text box on the form,txtUserID , so queries can see it.

public gvUserID 


sub form_load()
dim vLevel


 gvUserID =  Environ("Username")      'get userID,visible in all forms


'get level from user table
   vLevel = Dlookup("[Level]","tUsers","[userID]='" & gvUserID & "'")


'now, enable/disable items on form
   select case vLevel
         case "A"  'admin 
             'all is enabled


         case "U"  'normal user
             txtBox1.enabled = false
             txtManager.enabled = false


         case "M"  'manager
             txtBox1.enabled = false
   end select
end sub

Am kind of lost with your explanations here

[Emmanuel]

Would be glad if I could get help with the select if creteria

[orange]

Watch this youtube tutorial by Steve Bishop.

[Micron]

Is form A, B and C all the same except for what the user has permissions to? I wouldn't do that - it's 3x the work if you have to change something like how the form opens.
You don't put 3 values in the field in the user table. If these levels are accumulative from least to most (i.e. level3 would have all, level2 has permissions of 1 and 2 and level1 only has basic permissions) I would do something like (where Admin has all permissions)
tblLevels

LevelID	LevelOrder	Level	Description
1	5	User	basic
2	10	Manager	supervisory
3	15	Admin	all

To provide accumulative access to 2 levels on a form, WHERE LevelOrder < 11. To provide non-contiguous, WHERE LevelOder=5 OR LevelOrder=15 etc.
Your code could be simplified as DLookups rather than using a recordset like Ranman did. Gaps between these values allows for inserting future levels in between others.

If you could expand on what you didn't follow about that post we can probably add some clarity.
BTW, unless people who use the db share a Windows login, there is a way to avoid all this password stuff and keep everyone out. tblUser would have user LoginID. An invalid users id would not be found in the table, thus they wouldn't get in unless you left loopholes. In that case, a password routine is no better.

Forgot to mention that the autonumber field value from tblLevels is what gets stored in the user table.

[Micron]

13 minutes into the vid but have to pause for now. Anyone else wonder why you'd validate a user in a form load event rather than the open event? Open occurs first. User is invalid for that form. Now you have to close the form, or you had to make it invisible first. Why not check it in the open event, which can be cancelled?

[alansidman]

Here is tutorial that I used on several projects. It is a bit dated, but it did work for me for permissions.

https://www.mrexcel.com/board/thread...system.248191/

[Emmanuel]

Have been able to find a fix. Thanks to you all for your time