Smart Search without the Hassles

<< Click to Display Table of Contents >>

Navigation:  Forms  >

Smart Search without the Hassles

 

Nirmala Sekhar

No database application is complete without a comprehensive search facility. There are many methods of incorporating search routines in a form or a query. In this article, Nirmala Sekhar demonstrates a simple method of searching your data based on a given list of relevant fields. The example can be easily used in your database with very few changes.

AS I developed various applications in Access, I realized that almost all of my applications needed a search form. The earliest applications I developed had simple combo boxes in an unbound search form. Once the user had selected the values for the search, a command button would activate a query based on these combo boxes. The problem with this approach is that it involved a lot of hard-coding in the queries. And, of course, I had to make many amendments to the combo boxes as I moved from one database to the next.

As I spent some time thinking about this issue, I realized that although the fields and the tables varied from one application to another, the search process itself was more or less the same. The search process involved:

• A main search form

• The search to be based on a list of fields

• A subform that contained the resulting records

• The ability to choose the sort sequence for the resulting records

• An option to view all of the records (especially for smaller databases)

The ability to refine the search based on the current set of found records would be a nice feature to have (especially for databases with many thousands of records).

In Figure 1, you can see a sample search form that uses the Customers table in Northwind database.

Smart search

For my sample, I’ve used the Customers table from the Northwind database. The search form consisted of:

• A combo box that contains a list of suitable columns in the Customers table (see Figure 2)

• A combo box that defines where to look (that is, in the beginning of the field or anywhere in the field)

• A text box to input the search text
 
The code behind the search is very generic and can be used in any database just by changing the name of the table in the SQL command:   ...

 

200110_ns1

Figure 1. A sample search form.

 

Read the full article in PDF format here >> Smart Search without the Hassles

Download  
 
Your download file is called   Nirmala_Smart_Search.accdb  and you can download it on this page Forms >>