Data Management

An easier way to search all the text fields in a table at once

Clients are always looking for ways to make searching databases easier. Find out about one solution for Access 2000 that gets around the standard Find and Filter option limitations.

Helping clients quickly find the records they are looking for in a table is one of the most basic requirements in any database application you build. There are many ways to accomplish this, including look-ahead combo boxes and dialog boxes that search one or more fields at a time. A new client of mine who operates an art gallery had an unusual request for the Contact database application I built for him using Microsoft Access. He and his non-computer-savvy staff found the standard Find, Filter By Selection, and Filter By Form tools built into Access 2000 too rigid and difficult to use. Like most computer users, my client and his staff had more experience with the Internet than with classic office productivity applications. They wanted a tool like those found on most Web pages that allows a quick, full-text search of the site using a keyword.

I resisted the idea at first, explaining that this was a database they were working with, not a Web site, but eventually came up with the relatively simple solution I'll describe in this article. The basic principles should apply to most relational database applications with some minor rework as noted.

Capturing the search string
I began by adding the controls shown in Figure A to the top of the Contacts window. I used a text box and a label modified to resemble a hyperlink, but a button could just as easily be used to run the search, and code could be added to run the search if the user presses [Enter] instead of clicking on the "link."

Figure A

Because this was done in Access, there is a little bit of extra work to do in the code in Listing A. If this were done in Visual Basic, the form level variable m_strSearchText and the txtSearch_Change event code would not be necessary. In Access, of course, the Text property of the text box is only available while it has the focus, which is lost when another control, such as a button, is clicked. Focus cannot be lost to a label, however, so the Value property is not updated either, hence the workaround of storing the contents of the text box in a form-level variable. I'm certain there are more elegant solutions, but this worked well enough for my client.

The Keypress event filters out characters that either cause errors (a single quote character) or unexpected search results (# or * characters).

Running the search
The code that runs when Search is clicked is shown in Listing B. In this Access example, we're using the contents of the form level variable. If you did this with Visual Basic or some other language, you could simply use the contents of the text box control, checking for Nulls if necessary.

The code for the ExecuteSearch function is shown in Listing C. The SQL consists mainly of a sub-query that returns a list of record ID numbers. The OR and LIKE operators are used to check whether the search string passed to the function is found in any of the text/character fields in the table. Note that Access uses the asterisk (*) character as a wildcard with the LIKE operator (most SQL-compliant databases use the percent (%) character as a wildcard). Note also that a wildcard character is placed at the beginning and the end of the search string. This means that if the user searches for ton, the search will return all records containing this string of characters as any part of a field: Tony, Washington, Stone. This also means that any index on the searched field cannot be used to speed things up. Use of LIKE and OR in this way almost always forces a table scan.

Before the string stored in the variable strSQLWhere is passed to the OpenForm action of the DoCmd object as its Where Condition (a valid SQL Where clause without the word Where), it's concatenated into a full SQL command and passed to another function as strSQL. This function, FindRecord, is an old favorite of mine that takes a SQL string and does a quick check on the number of records returned. This example uses a DAO Snapshot recordset.

The same results could be obtained using ADO and the appropriate recordset type in Access or Visual Basic, or a stored procedure that takes the SQL string as a parameter and returns the number of records found as some type of integer in a client-server application. The purpose of the FindRecord function here is to avoid going to the trouble of opening a form if the recordset that will be the form's Recordsource returns no records. If the FindRecord function returns 0 (no matching records), the ExecuteSearch function ends and returns false to the calling procedure. When ExecuteSearch returns false, either due to no records being found or an error, the user is shown the Search Failed message box in the Click event for the Search label.

Displaying the search results
Access isn't always about extra work. If the search will return one or more records, ExecuteSearch returns true and the form frmSearchResults is opened using the variable strSQLWhere as the Where Condition parameter of the OpenForm action. Something similar could be accomplished with PowerBuilder by passing parameters to a window as it opens. Here things might get a little more complicated using Visual Basic.

The results are displayed in the Access form frmSearchResults. The default view is set to Continuous forms so that the resulting records will show as a vertically scrolling list. Similar results could be achieved with a bit more work using any one of the many grid controls available in Visual Basic or even using a combo box. The main idea is that the user will be able to scroll through the results of their search and then pick one of them. In the case of frmSearchResults, the form's Recordsource is a query that contains some of the fields from the Contacts table. ContactID is at the far right and its Visible property is set to False. The other two text boxes contain the calculated fields ContactNameBusinessName and FullAddress, created using the Expression Builder. These fields concatenate name and address information from the Contacts table into a more compact and quickly recognizable format. Finally, when the user locates the record they are looking for in the list and clicks the Details button, the code in Listing D runs. This reopens the Contacts form using ContactID = & ContactID as the Where Condition of the OpenForm action. The search criterion that the user entered stays visible in the Search text box.

Simple solutions are sometimes best
Although it's not exactly to my liking, this "keyword" search has proven very useful for this particular client who is too impatient for most conventional search methods. He would prefer to simply type Vienna and get a list of all his contacts in Vienna and is willing to ignore results with Vienna in fields other than City. He also finds it handy to enter part of a half-remembered phone number or name and then zero in on the contact he's looking for. Obviously, searching every field of every record in a table for a particular string of characters is very slow and inefficient and just not practical for very large tables, but there are ways to adapt this method to a larger client-server application that would minimize network traffic.





Editor's Picks