Filter a list box by clicking on another list box (1 Viewer)

[naterook]

Hey everyone.
This isn't critical, but would be a nice feature. I have a list box of clients and a list box of account managers that manage those clients.

Does anyone know a bit of code that would let me click on an account manager in their list box and it would then filter the client list box to show only those clients associated with that account manager?

 

[Ranman256]

lstClients is a listbox of all clients. (or combo). Rowsource = "qsClientsAll"

cboMgr has all managers. In the afterupdate event in cboMgr, have it swap out the lstClients query for a filtered one:

sub cboMgr_afterupdate()
if isNull(cboMgr) then
     lstClients.rowsource = "qsClientsAll"
else
   lstClients.rowsource = "qsClients1Mgr"
endif
end sub

changing the manager will re-list the clients. The query: qsClients1Mgr look like:
select [clients] from table where [manager]=forms!fMyForm!cboMgr

 

[The_Doc_Man]

Look up the term "Cascading Combo Box" which is exactly how you would approach this problem. NOTE that in the "Similar Threads" list at the bottom of THIS thread, there is such an article already selected.

In essence, you make the .RowSource for the 2nd combo box depend on data selected in the 1st combo box.