Syntax help

[Bill Johnson]

Can someone help me figure out what to use to search
through a table for a match then use that value?

I'm trying to add security to a particular field on a
form. Currently I'm using a Select Case statement to
evalutate the CurrentUser(), matching it to one of the
case statements then assigning a value to a variable based
on the match to allow the user to perform certain updates
on that field.

I would rather query a table of users, match the
CurrentUser() to the table and use a value assined in the
table.

Ideally, I would like to perform the same function based
on the security groups the users are assigned to.

Any ideas?

Bill Johnson
(e-mail address removed)

 

[Andy Cole]

Bill

Ideally, I would like to perform the same function based
on the security groups the users are assigned to.

Here's a routine to determine if the current user is a member of a
particular group. The only problem is that it requires 'hardcoding' a valid
Admin level user's details. Not a problem if you're distributing an MDE

Public Function CurrentUserInGroup(GroupName As String)
' Determines if the current user belongs to the Group passed
' as GroupName. Returns True if the current user is a member
' of GroupName, otherwise False

On Error GoTo err_CurrentUserInGroup

Dim wksMyWSpace As Workspace
Dim intI As Integer
Dim grpMyGroup As Group
Dim usrMyUser As User

' Create a new workspace as a member of the Admins group.
Set wksMyWSpace = DBEngine.CreateWorkspace("SPECIAL", "Valid admin User",
"valid password")

Set grpMyGroup = wksMyWSpace.Groups(GroupName)
Set usrMyUser = wksMyWSpace.Users(CurrentUser)
For intI = 0 To grpMyGroup.Users.Count - 1
If grpMyGroup.Users(intI).Name = usrMyUser.Name Then
CurrentUserInGroup = True
Set wksMyWSpace = Nothing
Exit Function
End If
Next intI
exit_CurrentUserInGroup:
CurrentUserInGroup = False
Set wksMyWSpace = Nothing
Exit Function

err_CurrentUserInGroup:
If Err = 3265 Then 'Item not found in this collection
MsgBox Err & " " & UCase(GroupName) & " isn't a valid group name", _
vbInformation
CurrentUserInGroup = False
ElseIf Err = 3029 Then 'Not a valid account Name or Password
MsgBox "The account used to create the workspace does not exist", _
vbInformation
Else
MsgBox "Groups - 'CurrentUserInGroup'. Error: " & CStr(Err.Number) &
_
" (" & Err.Description & ")"
End If
Resume exit_CurrentUserInGroup

End Function

Use it as follows;

If CurrentUserInGroup("Admins") Then
...do something
Else
MsgBox "You have no permission to proceed", vbCritical
End If