Override filter to show ALL records in listbox 2

[leicklda]

I have a listbox control in my form that displays product names [ProdSelect]. The names of the products which display at any time are filtered based on a category selection from another list box [CatSelect]. Example, when you click on soap, only soaps show. When you click on shampoo, only shampoos show.

I want one item in that list box, preferably a category called "ALL" that will show all products at once. How do I do that?

Here is the code which filters my ProdSelect control (Both controls are on the same form called "Startup":

SELECT Products.[Product Name]
FROM Products
WHERE (((Products.Category) Like [Forms]![startup]![ProdSub].[Form]![CatSelect]));


Thanks for any help!

 

[FoxProProgrammer]

One way to do this is to include the following If statement in the After Update event of the CatSelect list box.

Dim strQuery As String

If CatSelect.Value = "All" Then
strQuery = "Select Distinct Products.[Product Name] From Products;"
Else
strQuery = "SELECT Products.[Product Name] FROM Products WHERE (((Products.Category) Like [Forms]![startup]! ProdSub].[Form]![CatSelect]));"
End If

ProdSelect.Rowsource = strQuery
dz
dzaccess@yahoo.com

 

[danvlas]

There is a function called AddAllToList in Solutions.mdb sample database. Check that function, copy it into a module and use it for your list box.

Without using that function, you'll have either to use a code built value list for your list box (this is limited to 2048 characters if I remember well) or add a dummy product called "(All)" to your products.

Then use dz's suggestion.


Regards,

Dan