Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    USA
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Building Search (A2K)

    I was wondering does anyone know how to build themselves there own search engine in access. What I mean is, how would I create a search engine where I can type a word in a field and hit a button for it to run through the records and pull only those records I want by a Find Next button? Someone on a different forum told me this was extremely difficult and not to try doing it, so any help would be wonderful.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Building Search (A2K)

    Charlotte

  3. #3
    New Lounger
    Join Date
    May 2002
    Location
    Tasmania, Australia
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Building Search (A2K)

    Hi Brandon,

    I puzzled over this for some time and ended up using VBA to pull a search string from a text box in the header of a continuous form and combine it in a filter expression and apply it to the form. To search for a string contained in a field I used
    Me.Filter ="[FieldName] Like '*" & Forms![FormName].[TxtSearchString] & "*'".
    Me.FilterOn = True
    Me.Refresh

    Hope this helps

    Ryan

  4. #4
    2 Star Lounger
    Join Date
    May 2002
    Location
    USA
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Building Search (A2K)

    Charlotte, I checked out that site and I can't get it to work. I followed the instructions but for some reason when I run the search I still get those 830 records rather than the sorted records. Everything is right, I double checked it multiple times. Any ideas what I may be doing wrong?

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Building Search (A2K)

    I don't have a clue. I just built one in AXP and it performed as expected. Did you remember to put the form reference in the criteria line of the query? Each criteria expression should be under the field it applies to, but both should be on the line so they create an AND expression in the WHERE clause.
    Charlotte

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Try Filter By Form

    Brandon,

    Here's another suggestion: Filter by Form. This allows you to take an existing form and tell Access to allow you to filter the recordset based on why you type into the various controls. It is implemented by putting this line in the Open Event of the form:

    DoCmd.RunCommand acCmdFilterByForm

    That will open the form with empty-appearing controls. When you click in one of the controls, you'll get a dropdown of values in the underlying recordset or you can type in a value. Then right-click on the form itself to apply the filter. The form will display the records that match the criteria you entered for those fields. To change the critieria, right-click on the form again and select Filter By Form from the shortcut menu to get back to where you started.
    Charlotte

  7. #7
    2 Star Lounger
    Join Date
    May 2002
    Location
    USA
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Try Filter By Form

    I'll try this today, but I would like to get this Query by From to work. It could be possible that I am just not doing the criteria in the query right. The web page gives a couple of options. I chose to do what they had in the example. Now when I'd split it at the Or then it would creat two new query fields with what I put at the or. How do I put the criteria in the query. I think I've tried about every combination. Also I am using the northwind db, since that is what they suggest. Thanks for the help.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Try Filter By Form

    Here's a demo built on the MSKB article. It has the tables included, but they're simply copied from Northwind. Maybe you can figure out form this what you're doing wrong.
    Attached Files Attached Files
    Charlotte

  9. #9
    2 Star Lounger
    Join Date
    May 2002
    Location
    USA
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Try Filter By Form

    Thank you so much Charlotte. You have helped me multiple times and your advice is always good. With your example I saw what I was doing wrong. I was putting in the criteria wrong. Anyway I managed to get it working and again thanks for your help. <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21> <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>

Posting Permissions

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