Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010
    Posts
    4

    Unanswered: Open Form filtered with search criteria

    This is my code:

    DoCmd.OpenForm "recipeLocatorForm", , , "[recipeType]= '" & Me!recipeType_search & _
    "' AND [mainIngredient]= '" & Me!mainIngredient_search & _
    "' AND [ethnicity]= '" & Me!ethnicity_search & _
    "' AND [cookingMethod]= '" & Me!cookingMethod_search & "'"

    I have a search page with 4 combo boxes. If I choose criteria in all 4 boxes that match an entry, then it opens the form and it shows me the entry. If I choose criteria in 3 or less boxes, then it opens a blank form even if the criteria I've chosen matches an entry. It's acting like it needs to have info in all 4 combo boxes to work correclty. If one or more are left blank, then it doesn't work right.

    What can I do to be able to create a search form that will work with any combination of the 4 combo boxes? In other words, if I only choose two criteria, I want it to ignore the other 2.

    See attachment for a screenshot of the Search form.

    Thanks,

    Todd
    Attached Thumbnails Attached Thumbnails recipeSearch.jpg  

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by tap50 View Post
    This is my code:

    DoCmd.OpenForm "recipeLocatorForm", , , "[recipeType]= '" & Me!recipeType_search & _
    "' AND [mainIngredient]= '" & Me!mainIngredient_search & _
    "' AND [ethnicity]= '" & Me!ethnicity_search & _
    "' AND [cookingMethod]= '" & Me!cookingMethod_search & "'"

    I have a search page with 4 combo boxes. If I choose criteria in all 4 boxes that match an entry, then it opens the form and it shows me the entry. If I choose criteria in 3 or less boxes, then it opens a blank form even if the criteria I've chosen matches an entry. It's acting like it needs to have info in all 4 combo boxes to work correclty. If one or more are left blank, then it doesn't work right.

    What can I do to be able to create a search form that will work with any combination of the 4 combo boxes? In other words, if I only choose two criteria, I want it to ignore the other 2.

    See attachment for a screenshot of the Search form.

    Thanks,

    Todd
    Todd, Your code is working correctly for what you have written.

    If you leave teh control Me!recipeType_search empty, the value in the control is = to ""

    This will translate to:

    [recipeType]= ''

    which is not what you are really wanting want.

    One option to fix this is to leave out the criteria if the control has no value. This will require yiou to use VAB code to build the filter string. I prefer this method.

    If a field will NEVER be Null then you could use a dimplier method that will translate to:

    [recipeType] Like "*"

    Note: This will not include records where the [recipeType] is Null.

    To use the Like, try:

    Code:
    
    DoCmd.OpenForm "recipeLocatorForm", , , "[recipeType] Like  '" & Me!recipeType_search & _
    "*' AND [mainIngredient] Like '" & Me!mainIngredient_search & _
    "*' AND [ethnicity] Like '" & Me!ethnicity_search & _
    "*' AND [cookingMethod] Like '" & Me!cookingMethod_search & "*'"
    Hope this helps ...
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Jun 2010
    Posts
    4

    Thanks!

    Thanks, that worked!

  4. #4
    Join Date
    May 2010
    Posts
    601
    You're welcome!

    Glad we could assist.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

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