Solved Filter list box based on another list box

[zelarra821]

Hello. I want to make a plant search engine, in which I select the main function in a list box, in another list box add the contraindications that it should not have and the plants that correspond to those criteria appear in another list box. The main functions list box and the contraindications list box have to be linked, so that as you select main functions, the contraindications appear. Can someone guide me? I know how to link two combo boxes, but here arrays come into play and I don't know how to handle that.

 

[Ranman256]

listbox2 can be filtered using listBox1 via:

lstBox2 bound query uses ListBox1 as a param, is like:
select Cities from tCities where [State]=forms!fMyForm!ListBox1

then when user clicks on an item in ListBox1, just refresh box2:

sub lstBox1_Afterupdate()
lstBox2.requery
end sub

 

[Pat Hartman]

Cascading list boxes is identical to cascading combos. There are several common methods. I use the same one that @Ranman256 suggested. The RowSource of the dependent lst/combo has a where clause that refers to the lst/combo that is its parent in the Click event of the parent lst/combo, you requery the child.

I use the Click event rather than the AfterUpdate event because not all of these lst/combo controls are updateable. However, in this case, the BeforeUpdate, AfterUpdate, and Click will all perform the same function. I just think using the "update" events for some unbound control leads to confusion.

 

[CJ_London]

you don't don't need to go via the forms collection since you are already on the form so you could just use for the rowsource

select Cities from tCities where [State]=[ListBox1]

 

[zelarra821]

Thank you very much, guys, for your contributions.

I think you obviously know that it is a multi-selection list box, so doing what you propose, in my opinion, does not work.

I'm using an example from @MajP , and I've gotten it to work. My problem is that it generates duplicate fields in the second listbox, and I don't know how to fix it.

I leave you what I have achieved so far, to see if someone can help me solve that.

 

[CJ_London]

I think you obviously know that it is a multi-selection list box, so doing what you propose, in my opinion, does not work.

not obvious at all - you say

in which I select the main function in a list box,

tried to look at your db, it would have been helpful to not disable right click menus but having overcome that, it looks to me like your tables are not related correctly - you have a circular relationship and you are making extensive use of group by which implies there is something wrong with your structure or the logic you are trying to apply.

Good luck with your project but I think you need to review your app before going further.

 

[Pat Hartman]

I think you obviously know that it is a multi-selection list box, so doing what you propose, in my opinion, does not work.

Obvious? Why?

 

[zelarra821]

Hi guys. Let's go in parts.

not obvious at all - you say

Obvious? Why?

Here forgive me if I did not express myself well. I thought you were referring to a simple list box. So I didn't understand you well.

tried to look at your db, it would have been helpful to not disable right click menus but having overcome that, it looks to me like your tables are not related correctly - you have a circular relationship and you are making extensive use of group by which implies there is something wrong with your structure or the logic you are trying to apply.

I've redone the structure and now it works better without so much GROUP BY. In any case, what the woman who asked me for it thought she needed was not exactly this, this morning she explained it to me with a graph and it is something much simpler (or so I think).

So I want to thank all of you for your words, for always helping to the best of your ability.

 

[Pat Hartman]

We were giving you advice on a single select list/combo since it doesn't make sense to have multi-select combos cascade. I'm sure you can do it but I'm not sure how. You would probably have to build a union query using VBA. For each row selected in the first list/combo, you would need a select with an In() clause for the next level query. Visually, there is no way to control the second level to show the items for EACH of the first level selections. You could make the second level combo show ALL selections for ALL selected first level items but it would get very confusing visually.

 

[zelarra821]

Ok, thank you so much.