[AccessD] Searching Access

>>  Do tell, Do tell

OK, OK. A sample database is available. See below.


=================
AutoFilter Basics
=================
I call this solution the AutoFilter. 

AutoFilter doesn't build custom SQL statements on the fly. Instead, it
uses a standard query with a fixed WHERE clause. This query allows you
to return all rows, or restrict the results to any combination of field
values that you have elected to filter on. Even though the WHERE clause
is more complex than the custom one you would build in code, it often
queries significantly faster than a form using custom SQL. This is
because the query is pre-parsed, and only needs to be initialized once,
when the form loads. Custom SQL must be parsed and initialized every
time you change it.

Because the magic all happens in the query, no form-level filter string
is needed, either. Depending on how you implement it, you can even allow
the user to use the built-in Access Form Filter functions, on top of the
current AutoFilter.

You can put the filter controls and the query results on a single form.
I usually put the filter controls in a parent form, and move the results
to a sub form.

If your filter needs are simple (just a few filter fields), you can get
away with two lines of code to implement. I usually add a Clear Filter
function, which resets all the controls to Null, adding a few extra
lines of code. Implementing the Form Filter on top of AutoFilter
capability requires significantly more support code, with a small setup
stub in the parent form.


=====================
The Filter Expression
=====================
All right, but how does it _work_? 

AutoFilter relies on a simple convention: for a given field, Null
returns all rows, and a Value returns only rows matching that field
value. This makes it easy to use: select a value from a combobox, and it
restricts to that value. Delete the value, and it returns all rows. 

The trick is how this convention is encoded in the WHERE clause.
Remember that annoying property of Nulls -- how if any part of an
expression is Null, the whole expression becomes Null? Well, this WHERE
clause leverages that behavior. Specifically: 

    (Field = Null)  returns Null (always) 
    (Field = Value) returns True or False (depending on match) 

All you have to do is wrap this in a Nz() function, and you have the
basic form: 

    Nz((Field = FilterValue), True) = True 

This table shows how this expression works: 

    FilterValue     Null       3         0
    ===========================================
    Field  | 1 |    True     false     false
    Value  | 2 |    True     false     false
           | 3 |    True     True      false
           | 4 |    True     false     false

You can see how Null matches all rows, and values match any rows whose
field contains that value. For an actual control filtering an actual
field, the expression becomes: 

    Nz(([MyTable].[Field] = [Forms]![frmFilter]![cboField]), True) =
True 

Here, the current value of cboField filters matching values of
MyTable.Field. String multiple filter expressions together with ANDs,
and you have a WHERE clause that can filter any number of fields
simultaneously. How many, do you ask? I've gone up to about 8 or 9, with
little noticeable degradation in query speed. 


==========
An Example
==========
So, how is AutoFilter typically used? The classic case is filtering a
Primary table with a number of Foreign key fields: 

   tblOrders
   tblCustomers (CustomerID) 
   tblItems     (ItemID)
   tblSuppliers (SupplierID)

This data is ideal, because the fields being filtered on are Primary
Keys, and therefore can't be Null. AutoFilter can work on fields
containing Nulls, but you have to build a slightly more complex filter
expression to handle that case.


Building The Query
------------------
First, build a Result query that includes tblOrders, joined to
tblCustomers, tblItems, and tblSuppliers. Include the key fields you are
going to filter on, CustomerID, ItemID, and SupplierID. Include enough
fields to identify both the primary and foreign tables to the user. Save
it as qryFilterOrdersSub. Don't worry about the WHERE clause; we will
add it later.


Building The Forms
------------------
Build a Result subform, frmFilterOrdersSub. Set it to continuous forms
or datasheet view. Assign its RecordSource to qryFilterOrdersSub. Finish
by inserting the desired fields from the query, and arranging them so
suit your needs. Save the form. For fastest performance, do not use
comboboxes. The form should have Allow Filters, Edits, Deletions, and
Additions set to No. Save the subform.

Next, create your Filter form, frmFilterOrders. Insert a subform
control, subFilterOrders, that holds frmFilterOrdersSub. In the
frmFilterOrders' module, insert this code: 

    Property Get RequeryMe() As Boolean
        subFilterOrders.Requery
        RequeryMe = True  
    End Property 

Add three combo boxes: cboCustomerID, cboItemID, and cboSupplierID.
These simply query their respective tables, and are bound to their
respective key fields, CustomerID, ItemID, and SupplierID. In the Combo
Box properties dialog for each of your combos, insert this expression in
the After Update property:

    =[RequeryMe]

This calls the property RequeryMe, without having to build an event
handling routine. Save the form.


Finishing The Query
-------------------
Re-open qryFilterOrdersSub. Add this WHERE clause to the query: 

    (Nz(([tblCustomers].[CustomerID] =
[Forms]![frmFilterOrders]![cboCustomerID]), True) = True) AND 
    (Nz(([tblItems].[ItemID] = [Forms]![frmFilterOrders]![cboItemID]),
True) = True) AND 
    (Nz(([tblSuppliers].[SupplierID] =
[Forms]![frmFilterOrders]![cboSupplierID]), True) = True)

Save the query. You are now finished.

Looking At The Results
----------------------
Open the AutoFilter form. The results subform should show all rows,
since the initial values of the filter combos are Null. Select a value
from one of the combos. The subform should requery, returning only
matching rows. Delete the value of the combo, and observe that the
restriction drops off. You can easily filter any combination of values.

Notes
-----
* You can't do Form Filtering on this Result form. I got weird behavior
when I tried it. If you can get this to work, I'd like to know how you
did it.

* I use AutoFilter forms for lookup only -- no adding or editing data.
For that, I use a stand-alone form keyed to the selected record in the
AutoFilter form. 


================
Sample Available
================
I have a sample database that implements the simple form of AutoFilter
(no user Form Filter). Contact me off-list for a copy (Rich, yours will
be on the way tonight). I'm working up the coding for the more
sophisticated form, too. Let me know if you're interested. 

In return for sharing, all I ask is that my fellow list members not
publish this idea first. If you can't tell already, I'm thinking of
writing this up as an article.

-Ken