Login form and users permissions

[dfsabrown]

Hello

I have been searching for hours for some help with users & permission levels. I have ready a lot about ULS (User Level Security) which doesn’t seem to be my answer as I do not have admin rights and I believe it is not suitable with Access 2010.

Basically I have an 2010 Access database which is used by my department as a project management tool. It has various tables, forms, queries & reports, I have created a table with user names & passwords and a login form. So when the databases opens the login form is launched & the users selects their user name & enters their password, then the main menu is shown. Everything works really well, but as the moment there is no difference between all the users. All I want to do is to be able to make one of the Logins read only, ie if the “Reviewer” is logged in they can see & read every form and run every report but they can not make any edits.
:banghead:

Many thanks In advance for any help

 

[CJ_London]

There are many ways to address this depending on your requirements but the simplest is probably the following.

Assuming you have 'reviewer' and 'full access' in your user table 'access level' field then do the following.

1. In your login form, in your routine to check the user is allowed access, populate a global variable - we'll call it 'AccessLevel' as a text string. The global variable needs to be created in a module preceded with the word 'public'

2. in each of your forms where you want to restrict access put the following code

me.allowedits=AccessLevel="Full Access"

 

[dfsabrown]

Hi CJ London, thanks so much.

Please could you give me a bit more detail, i am a bit of a novice.

I have an 'Access level' field on the Login table & for each user except Reviewer i have put 'full access'.

on my login form this is my vba code to check the users are allowed in.

Private Sub cmdLogin_Click()

'Check to see if data is entered into the UserName combo box

If IsNull(Me.cboUser) Or Me.cboUser = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.cboUser.SetFocus
Exit Sub
End If

'Check to see if data is entered into the password box

If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.txtPassword.SetFocus
Exit Sub
End If

'Check value of password in tblEmployees to see if this
'matches value chosen in combo box

If Me.txtPassword.Value = DLookup("Password", "TBL_Login", _
"[ID]=" & Me.cboUser.Value) Then


'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon"
DoCmd.OpenForm "Control Panel"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
Me.txtPassword.SetFocus
End If

'If User Enters incorrect password 3 times database will shutdown

intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database.Please contact admin.", _
vbCritical, "Restricted Access!"
Application.quit
End If

End Sub


So please could you let me know how/where to do the global variable, AccessLevel as a text string.

Also the code me.allowedits=AccessLevel="Full Access" do i put that on the form(s) properties/Data/allow edits or somewhere else?

thanks so much

 

[CJ_London]

I really need to see your user table - tbl_login

 

[dfsabrown]

ID Login Password Access level
1 Manager *** Full Access
2 Investigator **** Full Access
3 Reviewer *** Read Only
4 Admin *** Admin

 

[CJ_London]

so, to be clear, your user chooses from a list of Manager, Investigator etc (I presume these are actually names) in a combobox and then provides the appropriate password? If it matches they get in and if not they are denied access.

Given this scenario, do the following:

Create a new module (or use an existing one) not a form module and add the following

Public gblAccessLevel as string

then in your login procedure insert the line in red where indicated

...
...
'Close logon form and open splash screen

[COLOR=#ff0000]gblAccessLevel=DLookup("AccessLevel", "TBL_Login", "[ID]=" & Me.cboUser.Value)[/COLOR]
DoCmd.Close acForm, "frmLogon"[COLOR=red] 
[/COLOR]DoCmd.OpenForm "Control Panel"

Else
...
...

How you want to handle it after that depends on you. for example in the above you might replace

DoCmd.OpenForm "Control Panel"

with

Select Case gblAccessLevel
Case "Full Access"
DoCmd.OpenForm "Control Panel"
Case "Read Only"
DoCmd.OpenForm "AnotherForm"
Case "Admin"
DoCmd.OpenForm "AdminForm"
End Select

Or you might in each forms open event put

me.allowedits=gblAccessLevel<>"Read Only"

or you might, also in the form open event disable a button by using

myButton.enabled=gblAccessLevel="Full Access"

etc, etc