Search table and display results in form

[KLR]

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.

 

[Allen Browne]

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.