Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Searching (2007)

    I currently have a form that is based off a query. The query is based from a table. The user input the required data and that data is fed into the table. That works fine. Now I need to create a form that will allow the user to search for data. I want the user to be able to search any of the fields. I am not sure of the best way to achieve this. I was thinking the form would have 20 text boxes and have the user input the field they want to search on. But I see problems in this. Does anyone know a better way? Is there a way to have one search box that can find any bit of information from a record in the table? Any suggestions will be greatly appreciated.
    Thanks,
    BZ

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

    Re: Searching (2007)

    Wouldn't it be easier to use the search and filter capabilities built into Access?
    - You can search in a specific field or in all fields, and specify whether the search text should match the entire field, the start of the field or any part of the field.
    - You can filter by selection, by form or use Advanced Filter to specify complex criteria.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching (2007)

    Yes that does sound much much easier. However I have never used filters before. What is the best way start? Create the search form? Do I set up the filter based off of each textbox? Will the new form be based of a new query?

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

    Re: Searching (2007)

    It's even easier than that - you don't have to do anything. The user can simply open the form used to view and enter data, and the built-in search and filter features will be available, some through right-clicking, others through the ribbon.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching (2007)

    That sounds easy enough. But there are several computers that will run this application using Access Runtime. When I tried to apply the filter in runtime, it doesn't work. Right clicking is not available. I even double checked and made sure i didn't have right clicking disabled. It is enabled and I still could not right click in access runtime.

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

    Re: Searching (2007)

    Yes, Access Runtime changes the situation.

    You could use a popup form to specify filter criteria. You'll find an example in ACC2000: How to Filter a Report from a Pop-Up Form. Although that MSKB article is for Access 2000, it applies to later versions too, and you can use it to filter a form instead of a report. Of course, you'll need to modify the code for your situation.

  7. #7
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching (2007)

    Thanks Hans... I will check the article out and get back with you.

  8. #8
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching (2007)

    I am using a few combo boxes like the example shows. But I also need to have a text box on the search form. I have a listbox on the search form that shows the results based on the search items selected. I want the user to be able to search on the subject of the PO that is stored in the database from a textbox. Here is the code on the search button:

    desc = "*" & Me.txtDescSearch & "*"
    strSQL = "SELECT [PO#], [Generated PO], [Description], Submitted, Rejected, [Approved Boss], [Approved Pay], [Paid], [I/E] " & _
    "FROM quPOlistboxsrch WHERE [Description] = 'desc' ORDER BY [PO#]"
    Me.lstPOlist.RowSource = strSQL

    I added the *'s hoping that the search would find all entries if the description isn't keyed in word for word. However this does not provide any results in the list box. Is there another way to achieve this? Like if the description in the database is "PO for Company Incorporated", I want that PO to be displayed if the user inputs "PO for Company Inc" in the search box.

    Thanks

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

    Re: Searching (2007)

    Use this:
    <code>
    ... WHERE [Description] Like '*" & Me.txtDescSearch & "*' ORDER BY ...
    </code>
    When using wildcards such as * and ? you must use Like instead of =.

  10. #10
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching (2007)

    Thanks... that worked

Posting Permissions

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