CheckBox/ComboBox to filter a ListBox based on a query?

[Sebsboutique]

Hello everyone, this is my first post...

First up, sorry if this seems like a silly question, but i am trying to filter an unbound ListBox of inventory based on a query running on a table called [Inventory]. The list box is already filtered by an unbound combo box to filter the list by a modification reference associated with the parts in stock (parts are sent to us assigned to a kit/modification). This ComboBox lists the mod references in a field in the [Inventory] table and you can select the stock allocated to that MOD.

I am trying to add a CheckBox to hide or show items that are in "0" stock. I originally had the query that populates the ListBox have >0 as the criteria. This was great and worked, but i need the stock line to be available to put items back into stock if not used by my teams. Best way i can describe it would be that i need a toggle to switch on the "0" stock, or hide it again. The stock level is held in the [Inventory] table in a field called [Inv_Qty] and we will call the query [MOD_Ref]. The ListBox is named [SearchResults].

I hope this makes sense... I have had a good old search around for some info on this, but it is quite possible i have missed something somewhere.

Any help would be greatly appreciated.

Many thanks,
Seb :0)

 

[bastanu]

Use your original approach (>0) and add code in the AfterUpdate events of both the checkbox and the combo to requery the listbox.

 

[arnelgp]

you need Query or VBA to filter the listbox.
on Query first:

select * from Inventory where [field1] = IIF(IsNull([Form]![yourForm]!combo), [field1], [Form]![yourForm]!combo) And
field2 = IIF(IsNull([Form]![yourForm]!checkbox), [field2], [Form]![yourForm]!checkbox)

where field1 and field2 are the fields you are filtering.

 

[Sebsboutique]

Thanks for your reply... I will look into this and let you know how it goes.

I understand the principal, but usually with me the placing of the code seems to confuse me... lol

 

[Sebsboutique]

Just wanted to thank you for your input people. I haven't quite got it nailed yet, but i am making progress. I am confident i can get it done now though, so thanks for the pointers! :0)