multiple seach critera

[dirtbiker1824]

Hi, I'm working on using access to store all of my recipies. I created three tables:

tblRecipes
>RecipeID(pk)
>RecipeName


>Directions

tblIngredients
>IngredientID(pk)
>IngredientName

tblRecIng
>RecIngID(pk)
>RecipeID(fk)
>IngredientID(fk)

because I can have multiple ingredients in each recipe I would like to seach for a recipe that contains certain ingredients. I can do this if I only want to look for one ingredient, but how do I set up my criteria for searching only recipes that contain a specified number of ingrients.
Example: I only want to display recipies that contain 'onion' & 'carrots' & 'potatos'

[jzwp11]

Doing what you want can be a little tricky. The best way I found to do it is to use an unbound form that has a multi-select list box & a button. Behind the button is some Visual Basic for Application code that dynamically creates a query that in turn, finds the specific recipes with the ingredients. Once the recipes (recipeIDs) are found they can be used to filter a basic form that shows the recipes. I figured that going through the process in a post might be hard to understand, so I created a sample database. When you open the database, you will be presented with a selection form (the unbound form I mentioned). I decide to give you two options, to return those recipes that include all ingredients selected from the list box (as you originally asked in your post) as well as a second option that returns recipes that include any of the selected ingredients.

The key to getting it to work for all ingredients selected is the query. In your case, you need a nested query. You can see the query I created in the code behind the button.

If you have any questions, please post back.