2 Combo Boxes (multivalued fields): the 2nd should not display values if already selected in the 1st

[Pavlina]

Hello, I appreciate your help with this scenario.

Scenario

I have a form with two combo boxes that have the same source table. Multiple values are allowed (I know this is normally not advisable, but in this specific case this is what we have).
What I want is that if certain values are selected in the first combo box, then they are not anymore available in the second.

Example:

Form [Customers]

Combo1


Control Source [Daily_Services]
Row Source table [Services]
"Service A"
"Service B"
"Service C"
"Service D"
"Service E"
"Service F"

Combo2
Control Source [Weekly_Services]
Row Source table [Services]
"Service A"
"Service B"
"Service C"
"Service D"
"Service E"
"Service F"

What I would need is for Combo2 to have as source a query that from [Services] EXCLUDES those already selected from Combo1 in that record. For example

If from Combo1 were selected as [Daily_Services]:
"Service A"
"Service C"


Then I want Combo2 to display as only available values for [Weekly_Services]:
"Service B"
"Service D"
"Service E"
"Service F"

And NOT to show "Service A" and "Service C" because they were already selected as [Daily_Services]

What I tried
I tried using a query as source for Combo2, with a criteria
NOT LIKE [Forms]![Customers]![Daily_Services]
This works only if there was a single value selected in Combo1 but it does not work if multiple values are selected. I tried both using as Source from [Services] the numerical ID and the actual text value.

Can you please help me to figure this out? I tried lots of complicated queries and SQL but I think I am complicating things when hopefully the solution is much simpler! Thanks a lot!

[June7]

I doubt the solution will be simple.

I don't use multi-value fields. I suspect the 2nd combobox RowSource will have to be a query that includes the 1st multi-value field in a query that expands the multiple values to individual records.

https://support.office.com/en-US/Art...n-US&ad=US#bm7