How to design User level security table for Forms

Question

I made a Table where I store information user and his Level, Example UserLevel 1, while giving him the front end.

There is one front end .accde for all users,

Every Form when it is opened it checks user's Level, if is allowed example if UserLevel = 1, then it will be in adding or editing mode, else it will be read only (means no addition, no deletion, no editing).

All data entry and reporting works fine.

But the problem arises when an User goes on leave and other user have to take care of his job, at that situation user level does not matches and he is unable to do the data entry.

There are more than 60 forms and around 8 users handling different sections.

Confused, how to handle it without doing any changes in the VBA coding of Form opening. So, Each Form can have more than one User Assigned to do the data entry.

Please advice, how to design my table of user level, so that if the user goes on leave, i can do changes in that level table and can effect on form.

Regards.

Answer 1

The only small, temporary change I can think of is to temporarily change their user level. 

Another alternative would be to temporarily change the regular user's password and have the fill-in login as that person.

Answer 2

In my situation, responsibility of the user on leave is distributed among few others. These others are already having their form on which they are editing/adding data.

Your advised method, need to create new login form or change in vba.

Was looking for a solution that only small temporary change in back end table can handle this.

Please advice is the alternative method suggestion is possible to be developed or it can be disadvantage on going through this way further.

Regards

Answer 3

But the problem arises when an User goes on leave and other user of other department have to take care of his particular job - example MachineReceiving Entry , at that situation user level does not matches and he is unable to do the data entry. Or If I change his level to 1 then he will have access to other form which should not be.

Are you saying that the user filling in may have a lower Access Level then the person who normally performs that function? 

If that's the case, then I would either temporarily up their Level or provide a special login name for such situations, then change the password when the regular person is back at work.

Answer 4

Sir,

Thanks for your reply.

I read your site and have learned earlier also and again I read for this topic. Nice and useful contents and example code.

My BackEnd is still in Access 2000. I work for front end on Access 2000 and windows XP. As some of the user are still in that version. Once front end is finalized, then I convert in mde and Access 2007 - accde and copy to the each user PC according to their version of office and refresh their link with data. And it is working fine.

That is the reason, I selected Office 2003 and Windows XP for question posting according the Master working PC.

Situation as explained in the above post.

Currently I am handling as below.

Table Name : USysTblLevel

ULevel	UserName	UserLocation	UserBranch	UserRemarks	UserValidDate
1	Irshad.Alam	DXB	MUSF	FULL ACCESS	27/08/2018

On Form open event it checks :

Private Sub Form_Open(Cancel As Integer)

On Error Resume Next

If DLookup("[ULevel]", "[USysTblLevel]") > 2 Then

Me.AllowAdditions = False

Me.AllowDeletions = False

DoCmd.RunCommand acCmdRecordsGoToLast

Else

Me.AllowAdditions = True

DoCmd.RunCommand acCmdRecordsGoToNew

Me.AllowDeletions = True

End If

End Sub

But the problem arises when an User goes on leave and other user of other department have to take care of his particular job - example MachineReceiving Entry , at that situation user level does not matches and he is unable to do the data entry. Or If I change his level to 1 then he will have access to other form which should not be.

What I was trying to gather an idea, like, to have another table Name "TblFormSecurity" having a fields : FormName, EditAllowedTo

Then to create record with all the Forms name, and User level which can edit, by putting level in commas

RecNo	FormName	AllowedUser
1	MachineHistory	1,2

so if the user Is required to increase of that form, then just add with comma. Like 1,3,9 only for edit/Add

Question arises, how to check this via vba at the time of opening the form.

Was thinking of some solution of taking -

a) not to disturb existing developed

b)Add a table mentioned above and with the help of below logic check the user level:

Dim arr() As String

arr = Split(DlookUp("[AllowedUser]","[TblFormSecurity]",",", Where FormName = Me.formName)

n = UBound(arr)

If DLookup("[ULevel]", "[USysTblLevel]") is available in arr() then

allow edit

else

No edit

Could not understand how to proceed further.

Please advice is it possible to develop the above method?.

It can be alternative approach to achieve the requirement?.

Regards

Answer 5

First, you chose Office 2003 as your Office version and Office for Windows XP as Windows version. But then you state; "there is one front end accde for all users". So which version is being used? You can't have an accde unless you are using, at least, Access 2007. The second thing wrong with that statement is using one front end for all users. Each user should have their own front end. sharing a front end invites corruption. 

You don't tell us how you identify the current user so you can know their user's Level. But it is highly unlikely that you can do anything without changing the VBA code. 

See my blog on Login Security with VBA which discusses techniques of using an assigned Access level value to determine what forms a user can use and at with what restrictions.