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
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