Forms - Accesslevels

[Sheba]

hi, I have about 10 data entry forms which will be used (read & write) by 6 persons. Four staff will have exclusive access to one form each while two staff will have exclusive access to two forms each. I need to set up permission levels so that each will only access forms that they've been authorised to use. Could someone advise me on how to go about this please? Thanks much in advance.

[ItsMe]

A login form would be what you need to implement first. With that, you could have your Main Menu always open while the app is open. In your Main menu you could have a textbox that would welcome the user and display their name. Another Textbox could hold the value of the Primary Key for the User record.

Rules could allow forms to open or not, based on the value of the hidden textbox with the PK value. You could use the .Visible property of a Command Button perhaps.

You will need to implement much more code to fully "Lock Down" an Access app. Restricting Access to form Objects is not easy.

[Sheba]

Thanks ItsMe.

I already have the login form and switchboard. I also have the forms and created the User Table. Since some people will have access to only one form and others to more than one form, I don't want to use permission levels like "Developer", "Admin", "Data Entry", etc because they will ALL have the same Data Entry access but to different forms.

Just needing guidance on how to create the table(s) structure concerning this and also, the code...

[ItsMe]

Probably a Select Case statement would be the solution. After you discover, who the user is, you can decide which forms open and or which controls on your switchboard are visible.
http://support.microsoft.com/kb/213630

[amrut]

Option 1-
Add a text field to the users table and add all the respective form names they have access separated by comma. Using the InStr() function, you can check when a button is clicked to open a particular form if that form name exists in this field.
Option 2 -
Add Yes/No fields to the users table, one each for the forms you want to grant/deny access. Check this field to grant/deny access.
Option 3 -
The above (Option 2) can be added to a User Level table, probably more preferable here as in the user's table, you need to set his user level only. The permissions are controlled by this table.

[orange]

You may wish to research Role Based Access Control whereby you determine which Roles have which privileges and assign Users to Roles.
Attached is a model of the tables and relationships involved.

This may be more than you need, but it may give you some ideas/concepts involved.

Good luck with your project.

[Sheba]

Many, many thanks ItsMe, Amrut and Orange. I will looks at these suggestions and repost an update.

[burrina]

Here is a DEMO from Arvin Meyer that you could adapt to suit your needs.

HTH

[Sheba]

Thank you burrina. This looks interesting

[Sheba]

Sorry it's taken me this long. My VB skills are still at beginner level. In my User table, I have a column for the categoryID and another for the AccessLevel where the accesslevel is has value list: Administrator, Supervisor, Developer, Editor, Reader Only.
Every user has a category ID assigned to their employee record in the User table. In the FormOpen event, I have the below code. It should check the category field of the User and if same as for the form, it will then call the Function, mySetSec.

However I'm getting a run time error message '3075' on the IF line. Could someone please advise what is wrong with it please?

Private Sub Form_Open(Cancel As Integer)
If DLookup("CategoryID", "tblUser", "[UserID]=") = "PD02" Or DLookup("CategoryID", "tblUser", "[UserID]=") = "All" Then
Call mySetSec(Me.Form.PaediatricForm)
Else
MsgBox "You are not authorized to view this form"
Cancel = True
DoCmd.Close acForm, "PaediatricForm", acSaveNo
End If
End Sub

[amrut]

Instead of using this code in form's opening event, use it in the code you use to open the form, most suitably a button. If the access is to be granted , open it else a message to user that he is not authorized.

DLookup("CategoryID", "tblUser", "[UserID]=") = "PD02"

the syntax for Dlookup is incorrect. The criteria part is incomplete.
DLookup("FieldName", "TableName", Criteria)

See here for syntax of DLookup http://www.techonthenet.com/access/f...in/dlookup.php

[eki einstein]

what i do for acces level in my ms acces app

1.build a table , Username(text),Password(text),acces level(number) - 1,2,3,4,ect its describe what acceslevel is the user have to acces the form later

2.make a login form
the login form will hide(if the user name and password are correct) when user have filled the user name and password and click login, its important not to close the form, because the login form will be a media to check a`data what user level is the username that login..

3.write a module, thet describe the acces level function.

you can wrote it like this
---------------------------------------

Sub level1()
If Forms!login!level > 1 Then
DoCmd.Close
MsgBox "you dont have insuficient acces to it'or what ever mesagge that will show when user doesnt have the minimum requirement for the acces level'", vbOKOnly, "Your app name"

End If
End Sub

----------------------------------------
if you have 4 level acces you can make those code into 4 time and replace the name off the sub as Level2(),Level3(),Level4(),etc

save the module and name it as you wish..., in my case I put all function that i need in one module and I name it "fungsi"(-this becouse im an Indonesian)

what important you need to know, in my case i put level 1 is have more acces than level 2 also level 2 is have more acces then level 3.
----------------------------------------
then what you need now is describe for every form what the minimum user acces level that can opened the form ,
Open the form Properties >> event>> on load>> "write the VB code to call the module such like this :"

This a code for level 1 acces to the form
-----------------------------------------------

Private Sub Form_Load()
Call fungsi'or What your module name is'.level1
End Sub

------------------------------------------------

Hope those help !, sorry if i had bad english

[Sheba]

Thanks Amrut & Eki.

The user accesses this form through a switchboard and I wouldn't want to mess with the macros embedded in the switchboard buttons hence I put the code in the OpenForm event. Most forms will have only one user authourised to use them. In the user table, I have a column called CategoryID. Prior to activating the access level, I want the code to confirm that the user has been assigned the category (e.g. OB01). If the criteria part of my DLookup is incorrect, could someone please help me with it?

If DLookup("CategoryID", "tblUser", "[strPassword]=" & Me.txtPassword.Value) = "OB01" Then
'Hide login form and open Switchboard
Forms!switchboard.Visible = False
Call mySetSec(Me.Form.ObstetricsForm)
Else
MsgBox "You are not authorized to view this form"
Cancel = True
DoCmd.Close acForm, "ObstetricsForm", acSaveNo
End If

[eki einstein]

If DLookup("CategoryID", "tblUser", "[strPassword]=" & Me.txtPassword.Value) = "OB01" Then
...............

i never use Dlookup but i think you need to read this
http://access.mvps.org/access/general/gen0018.htm

i try configure what are you trying to do.., my mind tell me that you put the value "OB01" are to Identified are the user have permission to open the form, Dlookup check are the Value(categoryID) stored in the user table for that user name are macht whit the me.txtpassword in the form that will be opened.. are that correct?

i think you should put the value "OB01" directly in Dlookup code

If DLookup("CategoryID", "tblUser", "[strPassword]= 'OB01'") Then
..................

but i must told you again, I have no experience using this Dlookup function

[eki einstein]

Ithink I got it now

If DLookup("CategoryID", "tblUser", "[strPassword]=" & Me.txtPassword.Value) = "OB01" Then

Your Dlookup value
DLookup("username", "tblUser", "[strPassword]=" & Me.txtPassword.Value

I think you must using that code on the comand button at the login form and the login form will oppened the form, let the form check it by it self are the user have the the permision to accesing it.

on load event the form will open, to check are the user have Correct Identity to acces the form I think you should wrote a VBA code again

If DLookup("Username", "tblUser", "[CategoryID]='OB01'" Then
msgbox "You are authorized",Vbokonly,"app name"
else
msgbox "Sorry .. ,You are not authorized",Vbokonly,"app name"
Docmd.close
end if