Using multivalue listbox to filter records via another multivalue field (1 Viewer)

Ok so I know that multivalue fields are the devil's spawn but I can't think of another way around it and I'm stuck!

I have a table, 'categories' that consists of categories of products for sale and their id numbers, eg 1 Wet Food 2 Dry Food 3 Small Breed 4 Large Breed
When a product is first brought into the store for sale it is given an ID number and it's details, including which categories it belongs to, are stored in another table APTD A product could belong to one or many categories.

I am now making a form that enables you to search for products and is full of various combo boxes and a text box (for keywords). I have managed to make the combos and text all work together so that they can be selected or not and the code creates the filter to put in the subform properties so that only the relevant results are shown.

I am stuck on the categories I have split the categories up into different groups, ie breed size, product type, but you could still have more than one selection so they have to be multifield (eg small or medium breed or sensiive and dermatological). So I want to select (possibly) multiple fields from breed_list and then have my subform (populated by a query called Get_words) check which products have these selections as part of their (possibly many) categories.

Clear as mud Of course, to be awkward, I also want to be able to do it so that if two items are selected on the search form I want to be able to search for both item a or b, and item a and b (user to check a box or something to decide which).

I have discovered that

For Each i In Me![Breed_list].ItemsSelected
criteria = criteria & ", " & Me![Breed_list].ItemData(i)
Next i
criteria = Mid(criteria, 2)
criteria = "Categories.value In (" & criteria & ")"
Forms!Combinations![Combinations options - subform].Form.Filter = criteria
Forms!Combinations![Combinations options - subform].Form.FilterOn = True

means that the subform will only show those results that have categories matching any of the search form selections. But to do this my query needs to include the .value field and so I start with multiple repeats of the same record.

thanks for any help

You need a junction table to store each category each product belongs to. It will only have the two foreign keys in it.

instead of multi value lists, another way to do this WITHOUT any vb code:
make a 'picked' table to hold the catagories chosen in the listbox.
user dbl-clicks the item in the list,
this runs an append query to add it to the tPicked table.
this table is joined to the data table and will only pull those items.

a delete query can empty the table (by pressing a button to 'Clear All')

another delete query can delete the 1 item in the 'picked' table when a user selects the item and presses the delete key.

Ah ok. I think you're both saying the same, or similar things. So instead of saving the categories for each product in a multi value field, save it in a second table where each line is one category for one product. So the product is repeated over as many records as it has categories?
I like, I like
Seems a shame to not be able to use the multi values as you can't help but feel they should make things easier, but they clearly don't!

Multi-valued fields do make things easier if you don't want to do anything remotely outside of very basic forms or queries.

As soon as you want to do something slightly complicated or involved, the methods required become exponentially more complex and difficult to debug. Your routines become long-winded to accommodate the issues they cause. They are a similar pain as look-up fields.

If you ever decided to upsize over to a SQL Server or MySQL storage backend you would have to re-write as they aren't a supported data type.