Ive been trying to change the recordsource of a report opened by a searchform with multiple varables including a recipeingredients subform. The buildfilter("Where") works well but does not join the record source of the report with recipeingredients. If i join in the record source the filter works well but gives multiple values of the same recipe for each recipe ingredient used.

I would like the searchform results to only be showen in the report is it possible.

I have included the eventprocedure for both the searchbutton and the printpreviewbutton showen here:

Private Sub PrintPreviewButton_Click()

DoCmd.OpenReport "Recipes", acViewPreview, "SELECT * FROM RecipeSearch " & BuildFilter("WHERE")

End Sub

Private Sub SearchButton_Click()

Dim strSQL As String, strWhere As String

If IsNull(Me.RecipeIngredient) Or Me.RecipeIngredient = "" Then
strSQL = "SELECT * FROM RecipeSearch"
strWhere = BuildFilter(" WHERE ")
strSQL = "SELECT RecipeSearch.* FROM RecipeSearch " & _
"INNER JOIN RecipeIngredients ON " & _
"RecipeSearch.IngredientID = RecipeIngredients.IngredientID " & _
"WHERE RecipeIngredients.RecipeIngredientID = " & Me.RecipeIngredient
strWhere = BuildFilter(" AND ")

End If

strSQL = strSQL & strWhere
Me.Recipes.Form.RecordSource = strSQL

End Sub

Was wondering if there was an easy way to get criteria with multiple filters including subform to show.

Can someone help me with this please.