Form field value based on access user group membership 1

[ragnarok75]

I have a database in Access 2002 which has workgroup security setup. I have a number of users who all belong to 1 or more groups. What I want to do is upon opening a form I want either a textfield or combobox on that form populated based upon the group membership of the logged in user.

Example:
User A in Group A opens form X and Field Y equals 1
User B in Group C opens form X and Field Y equals 3

Is this possible? If so can someone help me with the code?

Thanks.

 

[mp9]

For a function to determine if a user belongs to a specified group, check out

http://www22.brinkster.com/accessory/modules/002.shtml

 

[ragnarok75]

This looks like it is exactly what I need, but I need some help on implementation. It will be used in multiple places, so I tried to create it as a module which I could call and then run the result through a Select Case statement to get the desired effect. I having trouble correctly calling the module, and what value am I looking at in the Select Case statement? GroupName.Value? Am I even on the right track?

Thanks!

 

[stix4t2]

Maybe instead of select case you could use if then else statements

IF( isGroup("GroupName1"))Then
     'group1 execution
elseIF(isGroup("GroupName2"))then
     'group2 execution
end if

where isGroup is a public function

Function isGroup(GroupName As String) As Integer
'Code sample from Accessory

http://www22.brinkster.com/accessory

Dim w As Workspace, u As User, i As Integer
fctnUserGroup = False
Set w = DBEngine.Workspaces(0)
Set u = w.Users(CurrentUser())
For i = 0 To u.Groups.Count - 1
If u.Groups(i).Name = GroupName Then
fctnUserGroup = True
Exit Function
End If
Next i
End Function


Stix 42
Long Live Rock and Roll
Pop is for drinking

 

[ragnarok75]

I have used an If statement but when it tries to evaluate the isGroup Function it errors out on Dim w as Workspace, u As User, i As Integer - I get the error User-Defined type not defined on Workspace and User. Do I need to add another reference?

 

[mp9]

Set a reference to Microsoft DAO Object Library (3.6 or 3.5, depending on your version of Access).

 

[ragnarok75]

I am at a loss. I know that all of the posts are correct and if I could just figure out how to use them I know they would work.

Here is what I have:

A form opens and in the Form_Current event is the following code:

Private Sub Form_Current()

If (isGroup("GroupA")) Then
    Issuing.Value = "A"
    cmb_Sid.Enabled = False
ElseIf (isGroup("GroupB")) Then
    Issuing.Value = "B"
    cmb_Sid.Enabled = False
ElseIf (isGroup("GroupC")) Then
    Issuing.Value = "C"
    cmb_Sid.Enabled = False
ElseIf (isGroup("GroupD")) Then
    Issuing.Value = "D"
    cmb_Sid.Enabled = False
ElseIf (isGroup("GroupE")) Then
    Issuing.Value = "E"
    cmb_Sid.Enabled = False
ElseIf (isGroup("GroupF")) Then
    Issuing.Value = "F"
    cmb_Sid.Enabled = False
Else
    Issuing.Value = ""
    cmb_Sid.Enabled = True
End If

End Sub

Issuing is a textbox and cmb_sid is a combobox. Both are bound to the same field in the same underlying table.

Then I have the above function in the same form directly above this code. All the code runs, it seems to read only two groups 'Users, GroupX" which is ok, but GroupX never evaluates the right IF statement. Is it because the Function returns a True or False value? The intended effect is depending on the group a value on the form is filled in and not allowed to be changed, or if not a member of any of the groups then the value is blank and can be changed.

Thank you for everyone's help already, If you can see what I am doing wrong please let me know.

Thanks!!!

 

[ragnarok75]

I have found a workaround, however it uses username not groups. Any ideas on what I am doing wrong for the groups?

 

[PHV]

In the isGroup function replace all occurrences of fctnUserGroup with isGroup.
Tip: use the Option Explicit instruction.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886