Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Search box on Main Menu

    I am creating a Recipe Database and what I want to do is to put a search box on the Main Menu. Once the searched for item is selected, it would open the item in Form View. Ideally I would like to be able to enter multiple search terms and have it narrow down the search based on these criteria.

    For example, the user inputs Chicken, Potatoes and the results show a recipe for Chicken & Potatoes and also for Chicken Soup. I have a combo box on the Recipe form that searches for the recipe by name but I thought it would be cool if you could open the database and start a search right away.

    Thanks for any help.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    If you want "chicken, potatoes" recognised as two separate phrases, your code would need to split it up using the comma as the delimiter. So you would need to be consistent about entering search terms the right way.
    A simpler (but not as flexible) option would be to have (say) 3 text boxes: txtsearch1, txtsearch2 and txtsearch3.

    Something like this should work.

    Code:
    Dim strcriteria as string
    strcriteria =""
    If not isnull(me.txtsearch1) then
         strcriteria = strcriteria &  "([fieldname] like " & chr(34) & "*" & me.txtsearch1 & chr(34) & ") and "
    end if
    If not isnull(me.txtsearch2) then
       strcriteria = strcriteria &   "([fieldname] like " & chr(34) & "*" & me.txtsearch2  & chr(34) & ") and "
    end if
    If not isnull(me.txtsearch3) then
       strcriteria = strcriteria &   "([fieldname] like " & chr(34) & "*" & me.txtsearch3  & chr(34) & ") and "
    end if
    ' after this the last and needs to be removed
    If Len(strcriteria) > 4 then
    strctiteria = left(strcriteria, len(strcriteria)-4)
    
    end if
    if Dcount("*","tblrecipies",strcriteria) >0
      docmd.openform "frmYourForm",,,strcriteria
    else
     msgbox "No recipes meet the criteria entered."
    
    end if
    Regards
    John



  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts
    Hi John,

    thank you for your help. . . but I need more.

    I put three text boxes on the main menu. I wasn't sure what to do after that, so I created a commond button and then added the code to the command button, making the changes as needed. It gets to the line:
    if Dcount("*","tblrecipies",strcriteria) >0
    and then I get a message that says
    Compile Error:
    Syntax Error

    Not sure where to go from here.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    It is hard to tell from here.
    • Do you have Option Explicit at the top of the Module? After Option Compare Database? You should always have this.
    • Are there any typos in what I wrote? I can't test for these without building a db. I just saw one : strctiteria = left(strcriteria, len(strcriteria)-4)
    • Are all the names right? tblrecipies ? field names?
    • Add: "debug.print strcriteria" before the dcount line then use CTRL + G to display the immediate window.


    [CODE]
    Debug.print strcriteria
    if Dcount("*","tblrecipies",strcriteria) >0

    [/CODE
    Regards
    John



  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Here is a version that does work for me.

    Code:
    Dim strCriteria As String
    strCriteria = ""
    If Not IsNull(Me.txtsearch1) Then
         strCriteria = strCriteria & "([ingredients] like " & Chr(34) & "*" & Me.txtsearch1 & "*" & Chr(34) & ") and "
    End If
    If Not IsNull(Me.txtsearch2) Then
       strCriteria = strCriteria & "([ingredients] like " & Chr(34) & "*" & Me.txtsearch2 & "*" & Chr(34) & ") and "
    End If
    If Not IsNull(Me.txtsearch3) Then
       strCriteria = strCriteria & "([ingredients] like " & Chr(34) & "*" & Me.txtsearch3 & "*" & Chr(34) & ") and "
    End If
    ' after this the last and needs to be removed
    If Len(strCriteria) > 4 Then
    strCriteria = Left(strCriteria, Len(strCriteria) - 4)
    
    End If
    'Debug.Print strCriteria
    If DCount("*", "tblrecipies", strCriteria) > 0 Then
      DoCmd.OpenForm "frmrecipelist", , , strCriteria
    Else
     MsgBox "No recipes meet the criteria entered."
    
    End If
    • I needed another * after each search criteria
    • I needed a 'then' at the end of the Dcount line.
    Regards
    John



  6. #6
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts
    Hi John,

    Thanks again for your help with this. Sorry it has taken me so long to get back to you, I haven't had a chance to work on it until today. I have gotten farther but am still running into a problem. When I enter the search criteria and then click on the command button that I have attached the code to, (On Click) I get the following error message:

    Run-time error ‘2001’:

    You canceled the previous operation>

    End Debug Help

    And then when I click on Debug, the line:
    If DCount("*", "recipes", strCriteria) > 0 Then

    Is highlighted in Yellow.

    Not quite sure what I am doing to cancel the operation.

    I did add the line Option Explicit at the beginning, as you had recommended earlier.

    I am using Access 2002, if that makes any difference. Again, thank you for any help you can offer.

  7. #7
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    This might be helpful:

    Universal Search on many text with details

    Boyd Trimmell aka HiTechCoach
    Microsoft MVP - Access Expert


    Last edited by HiTechCoach; 2011-09-07 at 13:44.

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by donebb View Post
    Hi John,

    Run-time error 2001:

    You canceled the previous operation>

    End Debug Help

    And then when I click on Debug, the line:
    If DCount("*", "recipes", strCriteria) > 0 Then
    My code assumes you are looking in a field called Ingredients, and your little change assumes that these are in a table called Recipes.

    Are those assumptions correct?
    Regards
    John



  9. #9
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts
    No, and maybe that's where I am messing this up.

    I have a table called Ingredients, which has just 2 fields, IngredientID and Ingredient. The Recipes table has information about the recipe, Name, Description, Instructions, etc. Then I have a table called Recipe Ingredients, which has the IngredientID, as well as Quantity, Unit of Measure and the like. It pulls the information about ingredients from the Ingredients table and is tied to the Recipes table by the RecipeID.

    Here is a picture of DB from the Relationship window. Hope it can be enlarged and that it helps in figuring out what I am doing wrong. Thanks for your help.

    Recipes DB.jpg

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    OK
    I should have clarified the structure of your db at the start!

    So where do want the search to look, when the user types in Chicken Potatoes?
    Ingredient, Recipe Name, Recipe Description ?
    Regards
    John



  11. #11
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts
    I would like it to look in the Recipes table, but show the result in the Recipes form. (I know, it's not a good structure, after I get it working, I want to go back and change the names of the objects, such as tblRecipes, frmRecipes, etc.) Thx.

  12. #12
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I don't think there is any problem with the structure.
    The search needs to look in one (or more) specific fields in your table. If you look in the Recipes table for Potatoes you may not find it because potato is an ingredient, so it is not actually in the recipes table. So I would think that you want to search for recipes whose ingredients include potato.

    Another problem relates to spelling and plurals. Are you searching for potato or potatoes? It could be useful to replace the textboxes with comboboxes that use values from the ingredients table. That way if the ingredient is not in the combo you know there is no point searching for it.
    Regards
    John



Posting Permissions

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