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

 

[ErezM via AccessMonster.com]

hello
1. delete RecipeIngredient_AfterUpdate event
2. change SearchButton_Click to

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

3. then, change BuildFilter to:

Private Function BuildFilter(strStart as String) 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)=False Then
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 = strStart & varWhere
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If
BuildFilter = varWhere
End Function

good luck
Erez

 

[warrenjburns via AccessMonster.com]

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.