Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Search table and display results in form

Discussion in 'Information Technology' started by KLR, Jul 28, 2006.

  1. KLR

    KLR
    Expand Collapse
    Guest

    I want to set up a Search facility in my database that functions as
    below:-

    Users enter a search term into a text box on a form then click a
    command button to perform the search. All fields within the table are
    searched to find the keyword (search any part of a field not whole
    field).

    If the keyword isn't found, a message box should display saying the
    usual "No results found".

    Or if the term is found, then the results are displayed in a subform on
    the same form, along with command buttons to allow the user to view or
    print that record.

    What I need help with is the query/code that is required to get this
    initial search working and how to display the results in a subform. I
    am comfortable with creating the code to filter records for viewing.

    Any help would be greatly appreciated.
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Indian American Couple Donates For Sikh, Punjabi Culture Research In US Breaking News Aug 19, 2016
    Soul Search By Swarn Singh Bains Book Reviews & Editorials May 25, 2016
    Harpal Singh Kumar Cancer Researcher Knighted By Queen Elizabeth II Sikh Personalities Jan 5, 2016
    Movies Ashdoc's Movie Review---dozakh In Search Of Heaven ( 2015 ) Theatre, Movies & Cinema Apr 6, 2015
    Research Shows NFL Quarterbacks Play Better With Beards Business, Lifestyle & Leisure Nov 23, 2014

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    The problem here is the notion that you will search all fields for the
    keyword.

    It is possible to do that by setting focus to something that is not a bound
    control (even leaving focus on the command button that runs this code), and
    executing:
    RunCommand acCmdFind
    The user can choose Any part of field, or you can set the option for
    "General Search" under:
    Tools | Options | Edit/Find

    But that approach will not give you the filtered records. To do that, you
    need to create the Filter string, and that string will be field-specific.

    It would be possible to create a string such as:
    strWhere = "([Field1] Like ""*" & Me.Text0 & "*""") OR ([Field1 Is Null)
    OR ([Field2] Like ""*" & Me.Text0 & "*""") OR ([Field1 Is Null) OR ...

    But that is very inefficient, clumsy to build, and slow to execute.

    If you do have a data structure where many different fields could contain
    the keyword, you have repeating fields - a basic design flaw. You need to
    normalize the table, creating related *records* instead of many fields in a
    single record. This will be much easier and more efficient to query - both
    to build and to execute.

    It may be that the keywords themselves should be a further related table
    containing many keywords (many records) related to this new related table,
    but it is impossible to know that without knowing your design.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "KLR" <k.roberts@ucas.ac.uk> wrote in message
    news:1153137525.279540.233740@75g2000cwc.googlegroups.com...
    >I want to set up a Search facility in my database that functions as
    > below:-
    >
    > Users enter a search term into a text box on a form then click a
    > command button to perform the search. All fields within the table are
    > searched to find the keyword (search any part of a field not whole
    > field).
    >
    > If the keyword isn't found, a message box should display saying the
    > usual "No results found".
    >
    > Or if the term is found, then the results are displayed in a subform on
    > the same form, along with command buttons to allow the user to view or
    > print that record.
    >
    > What I need help with is the query/code that is required to get this
    > initial search working and how to display the results in a subform. I
    > am comfortable with creating the code to filter records for viewing.
    >
    > Any help would be greatly appreciated.
     

Share This Page