Multiple filter options using unbound checkboxes

[Hurumpf]

Hi
I have a database that holds recipes and I would like to be able to filter, in or out, certain ingredients.
For example show all recipes containing chicken or filter out all recipes containing nuts. I would also like to be able to filter with multiple criteria such as show all beef dishes that do not contain tomato, etc.

Each recipe has checkboxes to record which categories it should be included in and I have been trying to use unbound checkboxes in the header of a continuous form to mach these categories for filtering.

I can happily include or exclude one category like this.

 Private Sub chk_ExChicken_Click()
If Me.chk_ExChicken = -1 Then
    Me.Filter = "([Rec_Chicken] = False)"
ElseIf Me.chk_ExChicken = 0 Then
    Me.Filter = ""
End If
Me.FilterOn = True
End Sub

But I can't work out how to make the filter more complex to allow for the 10 criteria I would like to filter in or out. I have a nasty feeling that it may be too much for my limited programming ability but was hoping that someone may be able to help me by explaining it as simply as possible?

I hope I have explained things appropriately and am very appreciative of any help that can be offered.

Thanks

[Hurumpf]

I've posted in the wrong place haven't I? Sorry :(

[twinnyfo]

Hurumpf,

Nope, looks like you posted in the right place.

Before I move forward, I would like to ask how your tables are built?

In your block of code, it looks like there is a field in your table called Rec_Chicken. This implies to me that there are fields in each recipe for the type of ingredient?

Your recipes should have a table which holds all the data specific to just the recipe (RecipeName, Author, Instructions, PrepTime, etc.), and then you should have another table of just ingredients, which could hold information such as IngredientName, Calories, Cost, etc.). Then you have another table for all measurements. You have afinal table which marries those tables together, which would refer to the Recipe Table, and have a record for each ingredient and measurement used for that ingredient. Please refer to this thread for additional insight (Database Normalization)

This will not solve your immediate problem, but may help in the long run in designing your DB.

We can still address your desire to filter multiple ingredients.

[Hurumpf]

Thanks for the advice. I am in the process of building the database now so I will rebuild the tables as per your recommendations and have a look at the link. It will most likely avoid other issues later down the line when it would be much harder to do anything about it.
Essentially the desire to filter ingredients in and out comes from the need to speed the process of putting dietary plans together for clients. If I can filter out things that people don’t like or are allergic to and filter in things they like best, I can highlight suitable recipes easily and work from them.
Any insight you can give would be most helpful. I am mostly self-taught with regards to access and so unfortunately my teacher is pretty poor!
Thanks.

[twinnyfo]

I am self-taught, too. My teacher must be a moron!

:-)

[twinnyfo]

As I am considering your question, it is one that I have come across, but have never had to actually use it. My initial thought is that we will have to build a query programmaticall y and use that query as the record source for another Form.

Some of the other experts may have more experience with this type of query building, but I am glad to work through details and learn with you.

The basic concept will be to have a Form that establishes your search criteria, then build a query that finds all recipes that meet all those criteria (e.g. Give me all recipes that use chicken but without using curry or paprika).

The concept is pretty simple, but the details behind it can be pretty complex, because you have recipes that contain several different ingredients. I've been playing with the concept in my free time, today...

[Hurumpf]

Now that the ingredients are going to be in their own table it strikes me that I could probably filter using that table via unbound list boxes rather than a restricted set of tick boxes. That way if new ingredients are added they are automatically included in the filter. Does this sound like a better idea or am I making it more complicated?

[twinnyfo]

Hurumpf,

Now that the ingredients are going to be in their own table it strikes me that I could probably filter using that table via unbound list boxes rather than a restricted set of tick boxes. That way if new ingredients are added they are automatically included in the filter. Does this sound like a better idea or am I making it more complicated?

Keep in mind that "more complicated" doesn't necessarily mean bad. It is probably the "correct" way to create such a set of filters.

Although this would also "complicate " things, have you thought about having an "IngredientType " field in your Ingredients Table (along with an IngredientType Table)? This could list the type of ingredient it is, such as "Meat", "Dairy", "Spices", Vegetable", etc. This would also allow you to choose, for example, vegetarian recipes, or recipes without Dairy (or both). Again, this highly complexifies the entire project. It just depends on how much work you are willing to put into this project.

Just as an aside, even though the thought of creating a challenging recipe database appeals to my OCD tendencies, were there no good off-the-shelf recipe programs available? Many of these have such filters built in.....

[Hurumpf]

I had a look at the off the shelf software and it's way out of my price range. I am happy to spend time on this I just don't think I have the skills :(
Spent around 5 hours this morning looking at forums and trying different bits of code. I can happily filter in or out from an unbound combo box in the form header but I can't combine boxes to filter 1 ingredient in and one out, and I can't filter for more than 1 ingredient at a time.
I have tables for the recipe, for the ingredients and for the ingredient category as advised.
I made a query to show the recipes and the ingredients in a continuous form and tried to do all my filtering from there.
I tried using 1 unbound combo box to draw ingredients into different unbound textboxes and then pointing the filter at those boxes like this. (got the code from a Google search and tried to change it to suit... and failed. It filters the first criteria only and then returns no records after that. Maybe because I am trying to do it all on one field???)

 Private Sub btn_Filter_Click()
Dim strFilter As String
strFilter = ""
If Me!txt_Out1 & vbNullStr <> vbNullStr Then
   strFilter = strFilter & "AND [Ing_Ingredients] = '" & Me.txt_Out1 & "'"
End If
If Me!txt_Out2 & vbNullStr <> vbNullStr Then
   strFilter = strFilter & " AND [Ing_Ingredients] = '" & Me.txt_Out2 & "'"
End If
If Me!txt_Out3 & vbNullStr <> vbNullStr Then
   strFilter = strFilter & " AND [Ing_Ingredients] = '" & Me.txt_Out3 & "'"
End If
If strFilter <> "" Then
   Me.Filter = Mid(strFilter, 4)
   Me.FilterOn = True
Else
   Me.Filter = ""
   Me.FilterOn = False
End If
End Sub

So I can filter everything that contains chicken or I can filter everything that doesn't contain chicken.
I can't filter everything that contains chicken and carrot
I can't filter everything that contains chicken but not tomato

My brain hurts.

[twinnyfo]

Sorry it's taken a while to respond. I believe you will have to build a filter string on the fly that includes joins with other queries built on the fly. I am not at my pc right now, but I have played around with it a little recently. The basics are that you create a query that lists all the recipes that use chicken for example. Now if you want all recipes that don't use chicken, you create another query that left joins that query with the chicken query and set the criteria to be null at the join field

This may not make much sense in writing, but I can't write the exact code off the top of my head. I will try to return to this tonight if I get a chance.