Featured Sample - Form_SearchByMultipleListBoxes

Form_SearchByMultipleListBoxes

author: A. D. Tejpal

This sample db demonstrates application of search criteria based upon selections in three multi-select list boxes. For a given list box, the search criteria is deemed True if the field value happens to match any of the selected items. Overall criteria is determined by application of And operator between the outcomes pertaining to different list boxes.

For sake of illustration, three different data types are covered by the three list boxes in this sample, namely date, text and number. In case of text type, handling of embedded quotes if any, within the field values, has also been demonstrated.

Three different styles of displaying the contents of list boxes have been shown as follows:
(a) No cascading - The full list is always under display.
(b) Simple cascading (hierarchical) - LstDate controls the contents of LstType and LstSupp, while LstType controls the contents of LstSupp.
(c) FreeStyle cascade - Whichever list box happens to be updated, causes filtering of contents of other two list boxes accordingly.

For user's convenience, facility for selecting or de-selecting the whole list box has been provided.
Three different styles of applying the search criteria are demonstrated for each style of cascading:
(a) Permanent saved query with embedded criteria clause.
(b) SQL built at run time.
(c) Filter string applied at run time.

Selection results for each list box are stored in corresponding unbound text boxes as comma separated strings. For text type data, each element of such a string is enclosed within outer single quotes, after having replaced each embedded single quote if any, by double single quotes (As the outer enclosing character is single quote, embedded double quotes if any, don't require any special treatment). For date type data, each element of such a string is enclosed within outer hashes.

This arrangement eliminates the need for repeated cycling through the list boxes while testing the field values. The value is simply checked against the contents of corresponding unbound text box, using IN operator. In case of permanently saved query with embedded criteria clause, EVal() function is used for facilitating interpretation of superstring having IN operator.

Version: Access 2000 File Format

Find it here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=403