I was wondering whether somebody might be able to help me with a question I have on a MS Access Database I'm building.
I have created a search form with a few multiselect listboxes. What I'd ultimately like is for the selection(s) in one listbox to filter the second listbox, but am struggling to find a way to do this. I have looked at some of the other solutions on this forum but not been able to find one that works on my database.
My multiselect listboxes are:
1) lstRegion - Looking at table "Regions" with fields: "Region ID" and "Region"
(i.e. Asia, Europe etc)
2) lstCountry - Looking at table "Countries" with fields "Region ID" (to link to
Regions table), "Country ID" and "Country"
I have the following After-update code for lstRegion currently:
Expand|Select|Wrap|Line Numbers
- Private Sub lstRegion_AfterUpdate()
- Dim i As Integer
- Dim strIN As String
- For i = 0 To lstRegion.ListCount - 1
- If lstRegion.Selected(i) Then
- strIN = strIN & "'" & lstRegion.Column(0, i) & "',"
- End If
- Next i
- End Sub
I think what I need to do is add a line of code in this function that takes the region ID values stored in "strIN" and sets the row source of Countries Listbox to show only those Region IDs selected in Regions list box.
P.S I also have 2 command buttons for lstRegion that allow me to select all, or clear all selections made in the listbox. This is working fine :o)
Would be grateful for any suggestions.
Thanks in advance,
Amit
P.S Sorry I forgot to mention that each of the IDs in the String are separated as follows '1','2' etc. I think it's a comma delimited string or something along those lines.