Link to home
Start Free TrialLog in
Avatar of Senniger1
Senniger1

asked on

Access 2010 Code for If Member of Group Using Field in a Table

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

  Dim stDocName As String
  Dim stLinkCriteria As String
  Dim strFindEval As String
  Dim strFindStaff As String
  Dim Staff As String
  Dim Evaluator As String
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim UserName As String
    UserName = Environ("USERNAME")
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If UserName = "ABC" Or UserName = "DEF" Then
    strFindEval = InputBox("Enter Evaluator's Initials", Evaluator)
    strFindStaff = InputBox("Enter Staff Member's Initials", "Staff Member")
        stLinkCriteria = "[Staff]=" & "'" & strFindStaff & "'" _
        & " AND " _
        & "[Evaluator]=" & "'" & strFindEval & "'"
            GoTo Select_Form
        End If
    strFindEval = UserName
    strFindStaff = InputBox("Enter Staff Member's Initials")
        stLinkCriteria = "[Staff]=" & "'" & strFindStaff & "'" _
        & " AND " _
        & "[Evaluator]=" & "'" & strFindEval & "'"

Select_Form:
' Accounting
    If strFindStaff = "GAS" Or strFindStaff = "RJL" Then
        stDocName = "frmEvalStaffAcctDone"
            Else
        stDocName = "frmEvalStaffDone"
    End If


I need the code to use the data entered in the strFindStaff prompt and then see if this user is a member of the ACCOUNTING group.

I'm hoping to replace the following so that it looks to see if "GAS" OR "RJL" are members of a group (in this example ACCOUNTING).

' Accounting
    If strFindStaff = "GAS" Or strFindStaff = "RJL" Then
        stDocName = "frmEvalStaffAcctDone"
            Else
        stDocName = "frmEvalStaffDone"
    End If


The table name is STAFF and the group field is named GROUP.

Note I have code from a 7/11/2013 post on Experts Exchange labeled "Access 2010 Code for If Member of Group", but I couldn't get it working for this project.

Thanks in advance!
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Not easy to figure out what you are trying to do … and what is stLinkCriteria for?
Avatar of Senniger1
Senniger1

ASKER

stLinkCriteria is a field on my form frmEvalStaffAcctDone and frmEvalStaffDone

Simply what is need is this...
I have a prompt to enter someone's INITIALS.  I want to search the STAFF table for those INITIALS and then filter by what GROUP they are in.  

Table STAFF
   Initals     Group
   VCC         Accounting
   SFE         Docketing
   CDR         Docketing      

Therefore if I enter VCC and VCC is a member of ACCOUNTING, then I would write something like
If member of ACCOUNTING then
    do this
       else
    do this
 end if
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This was perfect.  Thank you so much!
You are welcome!