[AccessD] A2K: Filtering a pick list

On 3 Mar 2004 at 22:16, Darren DICK wrote:

> 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>
> 
Here's one way off the top of my head:

In the On_Exit of each combo call a function RebuildList()

Function RebuildList() as long
Dim strSQL as String

If cboSuppliers.ListIndex > -1 then
	strSQL = "SupplierID = " & cboSuppliers
End if
If cboBackType.ListIndex > -1 then
     if len(strSQL) > 0 then strSQL = StrSQL & " AND "
	strSQL = strSQL & "BackTypeID = " & cboBackTypes
End if
If cboRanges.ListIndex > -1 then
     if len(strSQL) > 0 then strSQL = StrSQL & " AND "
	strSQL = "RangeID = " & cboRanges
End if
If cboStyles.ListIndex > -1 then
     if len(strSQL) > 0 then strSQL = StrSQL & " AND "
	strSQL = "StyleID = " & cboStyles
End if

If len(strSQL)> 0 then strSQL = "WHERE " & strSQL

strSQL = "Select ChairID, SupplierID, BackTypeID, RangeID, StyleID" _
               & " tblChairs " & strSQL

lstChairs.Recordsource = strSQL
lstChairs.Requery

 
-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System 
Support.