A single search box capable of finding data accross many fields and delivering users to the appropriate form
As a professional trainer who teaches clients how to build MS Access databases, I have seen quite a few different database applications.
Good ones, have a few forms which can be easily accessed by a menu (switchboard) or better still a toolbar, with a variety of reports.
One function in a database that is usually missing however is a global search function.
It seems so simple yet most people neglect to add it. Those who do add a search function make it search just a single field, of a single table. For example, the search might find only the surname, or maybe just a client number.
But did you ever consider adding a multipurpose search function. One that can search multiple fields, across multiple tables. This makes it possible for users to search from a single search box for surname, given name, phone number, address, client number, date of event (booking), date of birth, anything that the user considers important.
One search box to search multiple tables provides a central jumping off point for your users
It really helps your users find the particular record and form they are after, rather than having to navigate through various screens, and then have to use scroll bars or Control-F.
The search function should be located in prime position on the main toolbar of the application so that it is always available no matter which screen the user has open or active.
I recommend a single box for the users to type in their search word or phrase, with a suitable small button inside the box on the right hand side. When the user clicks the search button, or presses the Enter key within the search box, the program searches the key fields in all tables and presents the results in a listbox on a screen that smoothly rolls down.
The Code Behind
The code behind the button contains a for next loop, a select case statement to build the sql query to the database, code to execute the query, and another select case statement to put the results into the list box. There is also a hidden listbox which contains the form and recordid to open.
The listbox_doubleclick event merely looks up the recordid and the form of the list item that was double clicked from the hidden listbox. It then opens the form displaying that record.
This gives your database application a single place to locate almost any data from a single search box.
Don’t forget to test the search term entered to see it doesn’t contain any special characters ( “ ’ ;/ ) that would cause use a database injection attack before executing the search.
