A user can be a member of more than one group of course. In fact they will
always be in at least two groups because all users are members of the default
Users group to which you'll have denied all permissions. So you'll need to
find out which groups the user is a member of, which you should be able to do
with the following function:
Public Function GetGroups(strUser As String) As String
Dim wks As DAO.Workspace
Dim grp As DAO.Group
Dim usr As DAO.User
Dim strGroupList As String
Set wks = DBEngine(0)
For Each grp In wks.Groups
For Each usr In grp.Users
If usr.Name = strUser Then
strGroupList = strGroupList & "~" & grp.Name
Exit For
End If
Next usr
Next grp
GetGroups = strGroupList & "~"
End Function
This will return a list of groups of which the user is a member, each group
name being separated by a tilde character. So you can get the groups for the
currently logged in user with:
GetGroups(Currentuser())
I'd suggest that on your form you set the Tag property of the group of
controls you want to lock/disable for the second group to the name of that
group. Let's assume the first group (no access to any control) is called
'Foo' and the second group (access to some controls only) is called 'Bar'.
Set the Tag property of those controls to which you want to disallow access
to Bar group members to Bar. Then in the form's Open event procedure put:
On Error GoTo Err_Handler
Dim ctrl As Control
Dim strGroups As String
Dim strAllGroupsButUsers As String
strGroups = GetGroups(CurrentUser())
' first check not in any other groups than Foo or Bar
strAllGroupsButUsers = Replace(strGroups, "~Users", "")
If strAllGroupsButUsers = "~Foo~Bar~" Or _
strAllGroupsButUsers = "~Foo~" Or _
strAllGroupsButUsers = "~Bar~" Then
' is user in Bar group?
If InStr(strGroups, "~Bar~") > 0 Then
For Each ctrl In Me.Controls
If ctrl.Tag = "Bar" Then
On Error Resume Next
ctrl.Enabled = False
ctrl.Locked = True
On Error GoTo Err_Handler
End If
Next ctrl
' is user in Foo group?
ElseIf InStr(strGroups, "~Foo~") > 0 Then
For Each ctrl In Me.Controls
On Error Resume Next
ctrl.Enabled = False
ctrl.Locked = True
On Error GoTo Err_Handler
Next ctrl
End If
End If
Exit_Here:
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here
Note that its important to test firstly for the user being a member of only
Foo or Bar (plus of Users of course which everyone is a member of) in which
case on the principle of the membership of the most privileged group applying
they'd have access to all controls, and then to check if they are members of
both Foo and Bar, as a user could be a member of both Foo and Bar, so, they
will, on the same principle, have the limited access to the controls.
Ken Sheridan
Stafford, England
