Hi,
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 ")
Else
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
Me.Recipes.Requery
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.
Warren.