Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search Form (2000)

    The attached file have the best search form sample. But I would like to have three search fields instead of one include any field. For instance, I want to have "CD Index, Date and Userfulness" three fields to let user enter. I tired many times but I can't make it.

    thanks for any help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search Form (2000)

    See the attached version. I have created separate text boxes for searching CD Index, Date and Usefulness, and corresponding hidden text boxes. I have modified qryCDNoAsc to work with the new text boxes.

    Important: I haven't modified the other queries. You will have to study qryCDNoAsc, then do the same for the other queries.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Form (2000)

    Thank you so much, Hans. This is what I want.

    Thanks

  4. #4
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Form (2000)

    Hi Hans,

    I really love this search function. But if table have more than 20,000 data, it will be very slow when I type the information in the search field. The button of form shows calculating. How we can do to speed it up?

    Thanks for your help, and you are the best.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search Form (2000)

    The present code will requery the list box each time you type something in any of the search text boxes. You could remove this code, and use a command button instead. That way, the user can enter text to search for in each of the three text boxes, then click the command button to search. See the attached version. (It could be simplified a bit, but I wanted to modify the design of the form or query as little as possible).
    You could also create an index on the CDDate field in the tblCDIndex table. This will speed up searching and sorting if the number of records is large.

  6. #6
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Form (2000)

    Hi Han,

    Thank you for your help. It really works great.
    But I have another question, I add one more column "OldCDIndex" in the tblCDIndex. After user input data in the "Search CD Index", search form will search on both "CDIndexID" & "OldCDIndexID". I did add "OldCDIndexID" on the form, but I don't know how I add in the query in order to get what I want. I tried several ways, but it doesn't work.

    Thanks

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search Form (2000)

    At the moment, the text box rather confusingly labeled "Search CD Index" actually filters on the CDNo field, not on the CDIndexID field. Would you like it to filter on CDIndexID and OldCDIndexID instead? Or do you want to add another text box?

  8. #8
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Form (2000)

    Hi Hans,

    Sorry for confusing. I would like to filter on CDNo & OldCDNo by one text box.

    Thanks

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search Form (2000)

    You can add a series of conditions to the queries. In the attached database, I have modified only qryCDNoAsc.

    Note: there are so many criteria rows now that you'll have to scroll down to see them all.

  10. #10
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Form (2000)

    Hi Han,

    Thank you so much for your help. It really works great.

    But I have another question, I add one more column "OldCDDate" in the tblCDIndex. After user input data in the "Search Date", search form will search on both "CDDate" & "OldCDDate". I did add "OldCDDate" on the form, but I don't know how I add in the query in order to get what I want. I tried several ways, but it doesn't work.

    To make it clear, I would like to filter on CDDate & OldCDDate by one text box.

    I am really appreciate all your help.

    Regards

    Thanks

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search Form (2000)

    That is done the same way you now search on CDNo and OldCDNo using one box. Unfortunately, the way Access organizes it in the query design window becomes cumbersome - each new addition doubles the number of criteria rows used. See attached version (as before, I have only modified qryCDNoAsc)

  12. #12
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Form (2000)

    Thank you so much, Hans. This is what I want.

    Regards

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Search Form (2000)

    What he could do is build the query qryCDNoAsc on the fly in VBA code.

Posting Permissions

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