Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2006
    Posts
    28

    Question Unanswered: Create a search field

    I need to create a search field on a form that will allow users to type in search criteria and then search across mulitple tabs within the same form. Can anyone help me?

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    search for what?
    searching in a table (or query) is fine
    but what will you look for in multiple tabs?
    surely each tab reflects some different subset of the data for one record.

    look for "Fred".
    where?
    in the phone number field?

    could you add some detail?

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jan 2006
    Posts
    28
    There are two primary tables tied to the forms that I want to search on. On each of these tables there are two memo fields. I want to be able to search each of these fields with the same function for any word entered in the search criteria. For example, I might want to see every record that has any mention of Blue Cross Blue Shield in one of those four fields.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    "every record" is not a tab thing, it's a table thing.
    i'm still profoundly confused by your enthusiasm for searching accross your tabs

    searching the tables themselves is another story:

    my way of doing it:

    given a textbox called "lookForMe" holding whatever you are looking for

    strSQL = "SELECT tbl1.field1, tbl1.field2, tbl2.field1, tbl2.field2 " _
    & "FROM <something that builds your join blah blah> " _
    & "WHERE tbl1.field1 Like '*" & lookForMe _
    & "*' Or tbl1.field2 Like '*" & lookForMe _
    & "*' Or tbl2.field1 Like '*" & lookForMe _
    & "*' Or tbl2.field2 Like '*" & lookForMe & "*');"

    replace <something that builds your join blah blah> with a valid source
    if you don't want to build your join manually, use A's query design mode to experiment with joining the two tables together. when the join is able to run in A's saved query, switch to SQL-view and copy/paste the join part over <something that builds your join blah blah>

    watch out not to drop crucial spaces.

    that SQL will sit happily as listbox.rowsourcce or querydef.sql etc etc

    maybe
    SELECT tbl1.field1, tbl1.field2, tbl2.field1, tbl2.field2
    is flakey. better might be:
    SELECT tbl1.pkField, tbl2.pkField
    and then use the pks to do whatever you plan to do with the search hits.


    alternative:

    use the A-query that you used to design the join.
    add Or criteria (i.e. separate criteria line for each field in the design grid) by clicking in the criteria box
    - then the magic wand thing on the toolbar
    - then navigiating in the dialog
    - find function Like and dbl-clk
    - replace the placeholder by navigating forms/all forms/whateverYourFormIsCalled/lookforme
    ...repeat for all four fields

    just for the joy of it - open that in SQLview and compare with the first alternative


    NB:
    searching inside memo fields is version dependent
    if it works in your version it is not the fastest experience you will have.
    Like query selecting four long memo fields with many search-hits will be glacial

    izy


    FORGOT THE SMALL PRINT: i'm not a bound form person. there is probably a perfectly slick way to do this with a filter or somesuch
    Last edited by izyrider; 03-24-06 at 14:02.
    currently using SS 2008R2

Posting Permissions

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