New Sample: Form_SearchAllFields

Form_SearchAllFields

by A.D. Tejpal

Explanatory Notes

This sample db demonstrates fast search across whole data source, covering all fields and all records. The search is conducted on all columns in "Across and then Down" style. Based upon space separated search words entered by the user, advanced search capability is provided as follows:

(a) Match field values having any of search words.

(b) Match field values having all of search words, but not necessarily in the same order as entered in search box.

(c) Match field values having all of search words, in the same order as entered in search box.

(d) Exact match of field values against full contents of search box.

As the user types into the search text box, equivalent criteria string gets displayed at bottom of form. Simultaneously, the total number of matches found across the data source gets displayed.

In addition, all matching fields get highlighted as follows:

(a) Light Maroon: If the record carries a match in any column and it also happens to be current record

(b) Light Green: All other records having a match in any column.

Various search options are selectable via an option group. Appropriate command buttons enable the user to find the first or next matching record as desired. A special subroutine ensures that the found record gets displayed at mid-position of the subform window.

In order to facilitate efficient use of FindFirst / FindNext methods of a recordset, different data columns are stacked into a single master column through a union query. This temporary query, generated in form's load event, is devised in such a manner that different fields of a given record appear as a set of contiguous single field rows. These sets of rows (a set representing one original record) follow the prevailing sort order of form's record source. This is achieved by using sequential numbers for records (as per their prevalent sort order). Within a given set of rows representing an original record, the sort order is based upon tab index of respective bound controls.

Note:
(a) While building the union query, care is taken that only visible bound controls (or unhidden columns in case of datasheets) are included in its output.

(b) For insertion of sequential numbers, conventional approach, using a subquery or recordset based function can prove un-acceptably slow for large data sets. In this sample db an exceptionally fast method using incrementing variable, with values stored in a collection, has been used. This is based upon the technique kindly suggested by Gustav Brock. Since values displayed by such a method tend to be volatile on navigation through normal query output, a temporary table has been used for holding the values.

(c) If the current record has more than one field matching the criteria, it will take that many extra clicks of FindNext command button to move over to next record having a match.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=517

[top]