Update one multiselect listbox when selecting items in another multiselect listbox

[AOB]

Hi there,

I have a form with two listboxes. Both are extended multi-select in nature and query-driven from two separate tables. There is a one-to-many relationship between these two tables (i.e. each 'parent' item in the first table can have multiple 'child' items in the second table; they are related via a common identifier)

I want to set up my listboxes so that when a user selects one item from the 'parent' listbox, the 'child' listbox is populated only with options for that selection. Similarly, if multiple items are selected from the 'parent' listbox, the 'child' listbox is populated with the options across all of those selected items.

So for clarity, let's call the two listboxes lstParent and lstChild and the respective tables tblParent and tblChild.

So I have as the RowSource for lstParent :

SELECT DISTINCT tblParent.Parent
FROM tblParent
ORDER BY tblParent.Parent

And the RowSource for lstChild :

SELECT tblChild.Child
FROM tblChild
INNER JOIN tblParent ON tblChild.ParentID = tblParent.ParentID
WHERE tblParent.Parent=[Forms]![frmMainForm]![lstParent]
ORDER BY tblChild.Child

But lstChild is never populated?

I tried adding this :

Private Sub lstParent_AfterUpdate()
  Me.lstChild.Requery
End Sub

But no effect.

Any suggestions as to how I can achieve this?

(Am aware I could use the AfterUpdate event of lstParent to loop through the selected items and construct the query for lstChild with VBA using a SELECT...FROM...WHERE...IN syntax but I hoped something like this would be a bit neater?)

Any pointers?

Thanks

AOB

 

[CJ_London]

you'll need to use a bit of vba to build your rowsource for lstchild which will be something like this to go into either the lstChild Enter event or the lstParent after update event - depending on your required program flow

lstChild.rowsource="SELECT tblChild.Child
FROM tblChild 
INNER JOIN tblParent ON tblChild.ParentID = tblParent.ParentID
WHERE tblParent.Parent in (" & MSListStr & ") ORDER BY tblChild.Child"

And you need to create this function as well in the form module

Function MSListStr() as string
dim s as string
dim i as integer
 
    s=""
    for i=0 to lstParent.rowcount-1
        if lstParent(i,0).Selected=True then s=s & lstParent.column(0) & ","
    next i
    MSListStr=left(s, len(s)-1)
 
End Function

 

[AOB]

Thanks CJ

Feared as much - oh well

This is my own version but pretty much the same jist as yourself. Thanks for providing yours!

Private Sub lstParent_AfterUpdate()
[INDENT]Dim strSQL As String
Dim itm As Variant
 
With Me.lstParent
 
[INDENT]Select Case .ItemsSelected.Count
 
[INDENT]Case 0
 
[INDENT]strSQL = ""
[/INDENT]
 
 
Case Else
 
[INDENT]strSQL = "SELECT [tblChild].[Child] " & _
[INDENT][INDENT]"FROM [tblChild] " & _
"INNER JOIN [tblParent] ON [tblChild].[ParentID] = [tblParent].[ParentID] " & _
"WHERE [tblParent].[Parent] IN ("
[/INDENT][/INDENT]
 
 
For Each itm In .ItemsSelected
 
[INDENT]strSQL = strSQL & Chr(34) & .ItemData(itm) & Chr(34) & ","
[/INDENT]
 
 
Next itm
 
strSQL = Left(strSQL, Len(strSQL) - 1) & ") ORDER BY [tblChild].[Child]"
[/INDENT][/INDENT]
 
 
End Select
[/INDENT]
 
 
End With
 
With Me.lstChild
 
[INDENT].RowSource = strSQL
.Requery
[/INDENT]
 
 
End With
 
[/INDENT]End Sub