cascading multiselect listboxes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have two multiselect listboxes on the same form, I would like the second
listbox to present its contents based on what is selected in listbox1, I am
able to do this as long as I use a combobox as box1, the problem is that I
would like to be able use multiple choices from box1. and get all those
choices into the parameter query used by listbox2.

I have tried to use examples of multiple selection for query based reports
but I cant make it work with my listbox2.


Many thanks

Albert
 
You would need to step through the selected items from your first list box
to create a SQL statement to be used as the Row Source of your second list
box.
 
Hi Duane,

Thanks for your comment, I believe I have that part of the problem, my
remaining problem is how to get the SQL statement into the Rowsource of the
second Listbox?!

Albert
 
I generally try to use generic code whenever possible.
facOperatingCompany is the field in tblFactory
the list box that has just been update is lboOperatingCompany
the "cascaded" list box is lboFacID

Private Sub lboOperatingCompany_AfterUpdate()
Dim strWhere As String
Dim strSQL As String
strWhere = BuildIn(Me.lboOperatingCompany, "facOperatingCompany", "'")
strSQL = "SELECT facFacID, facFactory FROM tblFactories WHERE 1 = 1 " &
_
strWhere & " ORDER BY facFactory"
Me.lboFacID.RowSource = strSQL
End Sub

'==== Generic Function in modControlCode ======
Function BuildIn(lboListBox As ListBox, _
strField As String, strDelim As String) _
As String
'send in a list box control object
' the list box name must begin with _
"lbo" and be followed by one character describing the data type _
"T" for Text _
"N" for Numeric or _
"D" for Date and then the 5th characters on for the _
field name ie: lboEmployeeID
Dim strIn As String
Dim varItem As Variant

If lboListBox.ItemsSelected.Count > 0 Then
strIn = " AND " & strField & " In ("
For Each varItem In lboListBox.ItemsSelected
strIn = strIn & strDelim & lboListBox.ItemData(varItem) &
strDelim & ", "
Next
'remove the last ", " and add the ")"
strIn = Left(strIn, Len(strIn) - 2) & ") "
End If
BuildIn = strIn

End Function
 
Back
Top