[AccessD] A2K: Filtering a pick list

No Archive
Thanks you all to those who responded
It's working like a charm
This list is Awesome

DD
----- Original Message ----- 
From: "Ken Ismert" <KIsmert at TexasSystems.com>
To: "'Access Developers discussion and problem solving'" <accessd at databaseadvisors.com>
Sent: Thursday, March 04, 2004 4:34 AM
Subject: RE: [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
> 
> 
> 
> 
> -- 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com