asked on

Access 2010 Code for If Member of Group

I have an Access 2010 database and a command button with the following code:

Dim stDocName As String
Dim stLinkCriteria As String
Dim strFindID As String
Dim EmpInit As String
strFindID = InputBox("Enter Staff Member's Initials (e.g. Your Initials)")
EmpInit = strFindID
stLinkCriteria = "[EmpInit]=" & "'" & strFindID & "'"
stDocName = "rptPTOLeft"
DoCmd.OpenReport stDocName, acPreview, "", stLinkCriteria, acNormal

I need the code to use the data entered in the strFindID prompt and then see if this user is a member of the MARKETING group. Then I'll use an If/Then statement like...

If member of MARKETING then
do this
else
do this
end if

I can use an Access Security group or an Active Directory Group (preferred method).

Can someone help me with this code.

Thanks in advance!

AccessGuy1763

Since you're using Access 2010, there really is no built in "Access security group". User level security was deprecated in Access 2007. I think, however, you're referring to creating your own design to implement some type of "Access security group", which you can definitely do.

I believe there is API you could utilize to get at an Active Directory user group.... I implement security in my systems by retrieving the AD username from Windows and it works pretty well but I don't utilize groups at all.

Here are two links which should help you get started:
http://stackoverflow.com/questions/11975228/how-to-iterate-active-directory-groups-roles-in-ms-access-vba
http://stackoverflow.com/questions/416856/determining-a-users-group-membership-using-vba

Senniger1

ASKER

Thanks. I found those links when searching myself before contacting Experts Exchange. I couldn't get it to work especially because I couldn't figure out how to incorporate the data from the prompt. Both examples are referencing current user.

Since I provided my code, can you alter my code based on your suggestion?

AccessGuy1763

Sorry, the reason I didn't provide specific code is because we're still discussing different designs and also the code may very well turn out to be somewhat long by the time we're done.

If prompting the user is absolutely required, you will probably need to have some type of employees table which will allow you to connect those initials to SOMETHING. Now... that something could be simply a "Group" field, or it could be their Active Directory login. If you go with just the "Group" field, there's no API calls to Active Directory required. If you want to really use Active Directory (your preferred solution) to determine if they're in an AD "Marketing" group/role, we're going to need to utilize code like the samples I linked.

My suggestion was more to totally throw prompting the user out the window and just plain retrieve their Active Directory username from Windows. This made a lot of sense to me if you really want to use AD groups to determine "Marketing membership" as prompting them for initials just to connect it to an AD username through a table you will need to maintain seems like extra and unnecessary work to me.

I can provide some samples for you but you should probably commit to a methodology first and I probably won't have time to write the code line for line.

Senniger1

ASKER

Just an FYI... What I'm trying to accomplish is to limit a department head from running reports on a user who is outside of her department.

I do have an EMPLOYEES table in this database and these would be the initials entered in the prompt. I'm okay with going the non-AD route if this would be easier.

Is this enough information for you to provide and example?

Thanks so much!

ASKER CERTIFIED SOLUTION

AccessGuy1763

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Senniger1

ASKER

Thank you so much for this.

In testing, no matter what initials I enter, strEmployeeGroup always = Error.

I went ahead and added a Group field to the Employees table. I know I'm limited in my VBA coding, but what part of this code does it check to see if the user initials entered is a member of the MARKETING group.

AccessGuy1763

Sorry for the delay in response... you caught me right before our all city golf tournament so I was quite busy over the weekend.

If strEmployeeGroup is always "Error", that means fcnCheckGroupMembership is having some issues. To get some clarity, you could add a message box to the Error_Handler like this:

   'Exit before error handling
    Exit Function
    
Error_Handler:

    'Return "Error"
    fcnCheckGroupMembership = "Error"

    'Inform User
    call msgbox("Error occurred in fcnCheckGroupMembership()" & vbcrlf & vbcrlf & _
                          "Error Number: " & err.number & vbcrlf & _
                          "Error Description: " & err.description
    
End Function

Open in new window

You also could use breakpoints to walk through the code line by line to try to figure out where it's going wrong... this code worked on my computer.

http://support.microsoft.com/kb/108438

Senniger1

ASKER

No worries on the delay. I'm going to be out of the office for a few day. I'll check this out next week and will get back to you.

Thanks again!

Senniger1

ASKER

Exactly what I needed.