BuildFilter

[warrenjburns]

Hi,

Can someone please help with this problem... I have a search form and im trying to incorporate a join as part of the BuildFilter to search for RecipeIngredients in the Subform of the form on the search form.

Here is the expression that works but the Private Sub RecipeIngredient_AfterUpdate() is a Search on the subform that works seperately to the BuildFilter and would like it to be part of the BuildFilter with the other selected variables and a single search click bring back the results.

Option Compare Database
Option Explicit

Private Sub ClearButton_Click()
Dim intIndex As Integer

Me.BeverageID = ""
Me.BeverageRecipeName = ""
Me.RecipeIngredient = ""

For intIndex = 0 To Me.BeverageType.ListCount - 1
Me.BeverageType.Selected(intIndex) = False
Next

End Sub

Private Sub PrintPreviewButton_Click()

DoCmd.OpenReport "Beverages", acViewPreview, "SELECT * FROM BeverageSearch " & BuildFilter

End Sub

Private Sub SearchButton_Click()

Me.Beverages.Form.RecordSource = "SELECT * FROM BeveragesSearch " & BuildFilter

Me.Beverages.Requery

End Sub

Private Sub Form_Load()

ClearButton_Click

End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varColor As Variant
Dim varItem As Variant
Dim intIndex As Integer

varWhere = Null
varColor = Null

If Me.BeverageID > "" Then
varWhere = varWhere & "[IngredientID] LIKE """ & Me.BeverageID & "*"" AND "
End If

If Me.BeverageRecipeName > "" Then
varWhere = varWhere & "[IngredientName] LIKE """ & Me.BeverageRecipeName & "*"" AND "
End If

If Me.RecipeIngredient > "" Then
varWhere = varWhere & "[RecipeIngredientID] = " & Me.RecipeIngredient & " AND "
End If

For Each varItem In Me.BeverageType.ItemsSelected
varColor = varColor & "[IngredientSubcategory] = """ & _
Me.BeverageType.ItemData(varItem) & """ OR "
Next

If IsNull(varColor) Then

Else
If Right(varColor, 4) = " OR " Then
varColor = Left(varColor, Len(varColor) - 4)
End If

varWhere = varWhere & "( " & varColor & " )"
End If



If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function

Private Sub RecipeIngredient_AfterUpdate()

Dim strSQL As String
If IsNull(Me.RecipeIngredient) Then
Me.Beverages.Form.RecordSource = "SELECT * FROM BeveragesSearch"
Else
strSQL = "SELECT BeveragesSearch.* FROM BeveragesSearch " & _
"INNER JOIN RecipeIngredients ON " & _
"BeveragesSearch.IngredientID = RecipeIngredients.IngredientID " & _
"WHERE RecipeIngredients.RecipeIngredientID = " & Me.RecipeIngredient & ";"
Me.Beverages.Form.RecordSource = strSQL

End If
End Sub

Please Help

Warren

[warrenjburns]

Hi,

Have got this far with my problem any suggestions....

Thanks Cybercow,

The Universal Search is cool and im sure i can use in it somewhere as it seems a powerful tool, but would like to keep all the expression i currently have if possible.

Have posted in other forums and have found the best solution has been to incorporate the RecipeIngredient_Afterupdate into the searchbutton but still have some syntax errors as showen here...

Hi,

This is going in the right direction and am very thankfull for your help but
has an error

Runtime error '3075':

Syntax error (missing operator) in query expression
'RecipeIngredient.RecipeIngredientID='.

Changed SearchButton_Click() as requested to:

Private Sub SearchButton_Click()
Dim strSQL As String, strWhere As String

If IsNull(Me.RecipeIngredient) Then
strSQL = "SELECT * FROM BeveragesSearch"
strWhere = BuildFilter(" Where ")
Else
strSQL = "SELECT BeveragesSearch.* FROM BeveragesSearch " & _
"INNER JOIN RecipeIngredients ON " & _
"BeveragesSearch.IngredientID = RecipeIngredients.IngredientID " & _
"WHERE RecipeIngredients.RecipeIngredientID = " & Me.RecipeIngredient &
";"
strWhere = BuildFilter(" AND ")
End If
strSQL = strSQL & strWhere
Me.Beverages.Form.RecordSource = strSQL

Me.Beverages.Requery

End Sub

Im sure this is getting closer, thanks for your time and help it means a lot
to me.

Warren.

What do you think?

[warrenjburns]

Hi,

I have a syntax error... the problem is when the Me.RecipeIngredient not selected the builder still requires a value for the join and or strSQL... I think?

When the Me.RecipeIngredient is selected the expression work very well and builder and select values from form and subform and BuildFilter accordingly.

The error is:

Run-time error '3075':

Syntax error (missing operator) in query expression
'RecipeIngredients.RecipeIngredientID='.

The expression is:

Private Sub SearchButton_Click()

Dim strSQL As String, strWhere As String

If IsNull(Me.RecipeIngredient) Then
strSQL = "SELECT * FROM BeveragesSearch"
strWhere = BuildFilter(" WHERE ")

Else
strSQL = "SELECT BeveragesSearch.* FROM BeveragesSearch " & _
"INNER JOIN RecipeIngredients ON " & _
"BeveragesSearch.IngredientID = RecipeIngredients.IngredientID " & _
"WHERE RecipeIngredients.RecipeIngredientID = " & Me.RecipeIngredient
strWhere = BuildFilter(" AND ")

End If

strSQL = strSQL & strWhere
Me.Beverages.Form.RecordSource = strSQL
Me.Beverages.Requery

End Sub

Please help

Warren.