Locking individual fields based on user logged in

[storl2]

Hi all,

I have a database that is used by 20 or so people now, but will soon be used by a hundred or so people (I know, Access is going to freak under the stress....). I need to put in some user level security that prohibits certain fields from being edited based on the login that the user inputs when they open the database. There will be three levels, admin (me, can do anything), data entry (very few, will be able to enter any data in any field), and normal users (everyone else, will be very limited as to what data they can enter). I know that Access can limit access to forms, tables, etc. but I need to be a lot more specific. I supposed I could split up some tables, but that would create some data integrity issues that I would rather not deal with. I was thinking of having a module that each form looks at when opened that sees who is logged in and locks/disables fields accordingly. Does anyone know of a way to have a module see who is currently logged in, or would I have to have them enter another login after the official Access one and store that somewhere? Does anyone know of a good way to go about this or have a better idea as to how to accomplish my goals?

In case anyone is wondering, everyone will need to be able to see all the data, and having a separate form just for data entry would kind of be a pain.

 

[lupins46]

Do you have your database split into FE/BE and does everyone have their own copy of the front-end?

 

[storl2]

The database is still a work-in-progress, so it is still in one piece. The forms will change pretty regularly since it is a fairly dynamic group, so I don't think I will split the FE/BE, at least not to the point where each user has their own copy. I see what you're getting at though, since it would be nice to just give them a copy of the FE with the proper permissions.

 

[FancyPrairie]

Here's one way to do it. When you setup security, assign the users to the 3 groups you specified (Admins, DataEntry, and Normal). And assign the appropriate rights to the various tables, forms, etc.

In the OnOpen event of the form, check to see which group the CurrentUser is assigned. If Admins group, do nothing. If DataEntry Group or Normal group, set the Enabled property to False for all fields they are not allowed to Edit (or set the Locked property to True).

To make it a little easier code wise, you could set the Tag property of each of the fields. For example, if Field1 can be edited by all 3 groups, then set the Tag Property to something like this: Edit=Admins,DatEntry,Normal; If Field2 can only be edited by Admins and DataEntry, then the
Tag property should be set to Edit=Admins,DataEntry;. In your code you can check to see which group a user is a member of and then loop thru all of the fields on the form and check to see if the group name exists in the Tag Property. If not, disable the control.

Note that a user could belong to more than one group.

Here's some old code that will tell you if a user is a member of a given group.

Function UserBelongsToGroup(strGroupName As String, Optional varCurrentUser As Variant) As Boolean

'********************************
'*  Declaration Specifications  *
'********************************

    Dim wsp As Workspace
    Dim grp As Group
    Dim usr As User

    Dim i As Integer
    Dim bolAnswer As Boolean
    
    Dim strCurrentUser As String
    
'****************
'*  Initialize  *
'****************

    On Error GoTo ErrHandler
    
    bolAnswer = False
    
    If (IsMissing(varCurrentUser)) Then
        strCurrentUser = CurrentUser
    Else
        strCurrentUser = CStr(varCurrentUser)
    End If
        
    Set wsp = DBEngine.Workspaces(0)
    Set grp = wsp.Groups(strGroupName)
    Set usr = wsp.Users(strCurrentUser)
    
'******************************************************************
'*  Loop to determine if the User is part of the group specified  *
'******************************************************************

    For i = 0 To grp.Users.Count - 1
         If grp.Users(i).Name = usr.Name Then
              bolAnswer = True
              GoTo ExitProcedure
         End If
    Next i
    
'    wsp.Close
   
'********************
'*  Exit Procedure  *
'********************

ExitProcedure:

    UserBelongsToGroup = bolAnswer

    Exit Function

'****************************
'*  Error Recovery Section  *
'****************************

ErrHandler:
   
    If Err = 3265 Then
        MsgBox UCase(strGroupName) & " isn't a valid group name", vbExclamation

    ElseIf Err = 3029 Then
        MsgBox "The account used to create the workspace does not exist", vbExclamation
         
    Else
        MsgBox Err.Description, vbExclamation
    
    End If
    
    wsp.Close
    GoTo ExitProcedure
 
End Function

 

[FancyPrairie]

You're going to be better off and safer if you split your database. FE on user's machine, BE on server. With that many users using the FE, it can easily become corrupted and bring everybody down. There are several other reasons for a FE/BE.

Also, there are several threads that show how you can automate the process of downloading a new FE to each of the users.