[AccessD] A2K: Filtering a pick list

Hello All,

I use something for a few dbs to allow the user to use search criteria on 
multiple fields...the can use just 1 or all 5...this technique could be used 
to filter a combo box...

Lets say I have 4 dropdowns that I want to use as criteria for the main 
dropdown...I would create the following fields on my form:

Drop1
Drop2
Drop3
Drop4
MainDrop
Drop1Wild
Drop2Wild
Drop3Wild
Drop4Wild

All 'Wild' fields will be hidden and default value = '*'

On the AFTERUPDATE of the Drop1-4...populate the corresponding 'wild'...Ex:
*****
Dim Drop1Wild

    Drop1Wild= Me!Drop1.Text & Chr(42)
    Me!Drop1Wild= Drop1Wild
    Me!MainDrop.Requery
*****

Then on my ROW SOURCE of MainDrop I would use something like:
********
SELECT DISTINCTROW tblChair.ID
FROM tblChair
WHERE (((tblChair.Drop1) Like [Forms]![frmPickChair]![Drop1Wild]) AND 
((tblChair.Drop2) Like [Forms]![frmPickChair]![Drop2Wild]) AND 
((tblChair.Drop3) Like [Forms]![frmPickChair]![Drop3Wild]) AND 
((tblChair.Drop4) Like [Forms]![frmPickChair]![Drop4Wild])
ORDER BY tblChair.ID;
*******
This will will allow them to use any combination of the other dropdowns you 
provided...and even if they remove a value they entered...the sql will see 
it as just a wildcard...

I'm sure, as always, there is a simpler/better way...but it works for me.
Hope it helps...

Thanks,

Mark A. Matte


>From: "Ken Ismert" <KIsmert at TexasSystems.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "'Access Developers discussion and problem 
>solving'"<accessd at databaseadvisors.com>
>Subject: RE: [AccessD] A2K: Filtering a pick list
>Date: Wed, 3 Mar 2004 11:34:11 -0600
>
>
>   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
>
>
>
>
>--
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
Get a FREE online computer virus scan from McAfee when you click here. 
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963