Access Form VBA filter with multiple items in a field

[niloufar]

On My query I have a field with multiple Names. Some are Managers and some are Auditors.

1. On my form, I added a field with two Items , Managers and Auditors. So, when I select Managers I would like to filter my subform and query with 5 names in my record and if I select Managers, I would like to be able filter my form/query with 3 names in my record.

below is my vba code, but it's not working,

Private Sub cboAuditor_AfterUpdate()

    strManagers = "<>'Steven' And <>'Lisa' And <>'Christopher' And <>'Ronda' "
    strAuditors = "'Steven' Or 'Lisa' Or 'Christopher' Or 'Ronda' Or 'Amber'"


        If Me.cboAuditor = "Managers" Then
            strAnalystFilter = "[Created By] = '" & strManagers & "'"

        ElseIf Me.cboAuditor = "Auditors" Then            
           strAnalystFilter = "[Created By] = '" & strAuditors & "'"

        End If

    End Sub

[orange]

Do yourself a major favor and do not use multi-valued fields in tables. Why M$oft did this is beyond me. It is not a concept used anywhere outside MSAccess.

If you have a table of Auditors, then make a record for each Auditor

tblAuditor
AuditorID autonumber PK
AuditorFirstName text
AuditorLastName
other auditor Info

Review the use of listbox (on forms). You can select multiple items from a list box.

If I have misunderstood your post, please provide more info and an example of input and expected output.

[niloufar]

Actually the Auditors List is too long and I do have auditor table.

But, what I need is instead of having 30 name to be selected each time for Auditors in the list box, I only select Auditors on my form then the VBA select them from my table or if I have it hard coded like my code. same thing for Managers.

I don't have position field in my query, and I don't want to use Listbox that is too much to select all names, so I want to associates those names to either as Manager on Auditors in the form.

[orange]

I noticed your post on another forum --you should let people on a forum Know you have posted elsewhere. It's considered good etiquette and readers can check to see if you have an answer.
That saves people who are volunteering their time and experience to help you from working on an issue that has been resolved.

I also notice in other posts that you work with Sharepoint.

Is this current issue related to Access desktop or sharepoint?

[niloufar]

yes, but my question is on another website and no I have not received any answer yet.

LOL, you can be an good investigator

and Yes, I do work with sharepoint, but his is not related. This suppose to be a simple form in access 2010 , but I can't put my head around this to figure out a good solution.

[orange]

I saw in one of your databases you have a field named Position with values

APT
BedRoom
Position
Location
Duedate

56AB
4
enginer; doctor; teacher; nurse
SouthSide
23/07/2014

3AS
3
engineer; doctor; nurse
NorthSide
31/07/2014

2A
2
Nurse; Teacher
SouthSide

This is not how you put data in a field in a normalized table.

I don't know if this is a concept you are working on now in the current post, but you could tell us specifcally WHAT you are trying to accomplish, and let readers offer focused suggestions for HOW it could be done.

As I see your current post (from guessing and 30000 ft) you should have a table of Auditors and a table of Managers.
Putting multiple values in a field is contrary to relational database design.

Also,see this regarding posting on multiple forums and why letting readers know is a good practice.