[AccessD] A2K: Filtering a pick list

  I'm a man, who manufactures,
  who manufactures chairs.
  I'm a man who manufactures
  chairs and other wares.
  I'm a man, who manufactures!

There is an efficient way to do this without building a custom WHERE clause.
You need to make a WHERE clause for your original listbox query that works
for every combination of combobox values.

Take cboSuppliers, for example. You want it to restrict to a single Supplier
when a value is selected, but show all Suppliers when no value (Null) is
selected. A WHERE expression that does this is:
  WHERE Nz(tblChairs.SupplierID = [Forms]![frmChairs]![cboSuppliers], True)
= True

When cboSuppliers has a value, this will return chairs only for that
SupplierID. When cboSuppliers is Null, the Nz function will return True for
all SupplierIDs. (Value = Null returns Null in all instances.)

So, your full WHERE clause would be:
  WHERE (Nz(tblChairs.SupplierID = [Forms]![frmChairs]![cboSuppliers], True)
= True)
    AND (Nz(tblChairs.BackTypeID = [Forms]![frmChairs]![cboBackTypes], True)
= True)
    AND (Nz(tblChairs.RangeID = [Forms]![frmChairs]![cboRanges], True) =
True)
    AND (Nz(tblChairs.StyleID = [Forms]![frmChairs]![cboStyles], True) =
True)

In the AfterUpdate event of each combo, your code to implement couldn't be
simpler:
  lstChairs.Requery

You could also introduce a Clear button, which would reset your filter in
its Click event:
  cboSuppliers.Value = Null
  cboBackTypes.Value = Null
  cboRanges.Value = Null
  cboStyles.Value = Null
  lstChairs.Requery

You will find this faster than forcing the listbox or subform to parse the
RowSource SQL every time a filter combobox changes. This works well even for
large tables, as long as the fields you are filtering on are properly
indexed.

Note that this technique works best for fields that do not allow nulls, like
keys. With slight modifications, you can get it to work for nullable fields.

Plus, for subforms, you have the advantage of allowing the user to filter
the subform themselves, since you are not using the form's Filter property.
This has saved me from having to recode a form because someone wanted a
custom way of looking at the data.

-Ken

(my apologies to Mister Rogers)


-----Original Message-----
From: Darren DICK [mailto:d.dick at uws.edu.au]
Sent: Wednesday, March 03, 2004 5:17 AM
To: AccessD List
Subject: [AccessD] A2K: Filtering a pick list


Hello all
I have a very large list box that lists all the products (in this case
chairs) in a db
Each chair in tblChairs has many fields but in this case the ones I am
interested
in are ChairID, SupplierID, BackTypeID, RangeID, StyleID

So on the same form as this very large list box of chairs, I have combo
boxes -
yep you guessed it - 4 of 'em - cboSuppliers, cboBackTypes, cboRanges,
cboStyleID

I wanna filter the large list box based on the values in all or any
combination of the combo boxes.

EG
Say supplier 5 = Acme Manufacturing and in the whole dB there are 100 chairs
made by Acme
Say of that 100 that Acme produce 38 of em have a Backtype of 2 (as in Low
=1 medium=2 High=3 etc)

I would want the large list to instantly decrease to 100 items if I selected
Acme from the Suppliers combo.
Then I would want that list to further decreas to 38 when I choose Medium
back
PSUEDo = show me evert chair in the dB with supplier ID = 5 and BackType = 2
etc etc and then somehow accept the various combinations of combo choices.

Is there a clever or easy way of doing this without writing a zillion sql's
then selecting the correct one
and making it the listbox row source? <yech>

Many thanks in advance

Darren