Results 1 to 4 of 4

Thread: BuildFilter

  1. #1
    Join Date
    Apr 2009
    Posts
    4

    Unanswered: BuildFilter

    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

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It's difficult to give you a pertinent answer without knowing the kind of data you handle. For instance, what does the Right(varColor, 4) function and the like do? As I can only guess, you seem to agglomerate different types of data (numeric and text at least) together, which is ok because you use variant variables but I cannot figure out what these data are, and I can only guess how the tables are structured.

    Have a nice day!

  3. #3
    Join Date
    Apr 2009
    Posts
    4

    BuildFilter

    Hi,

    Have created a SearchForm which filters my recipes.Form created from several tables and the recipes form has a subform of recipe.ingredients.

    Find attached a snapshot basically using combo boxes to filter the recipe.form but having difficulty searching the subform recipe.ingredients as part of the overall search.

    latest development is...

    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?
    Attached Files Attached Files

  4. #4
    Join Date
    Apr 2009
    Posts
    4

    BuildFilter

    Sorry, wrong attachment, find updated.

    Warren
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •