Transferring ID from one Multiselect Listbox to another

[asharma0001]

Hi all,

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:

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.

[asharma0001]

SQL for Countries table is:

SELECT Countries.[Region ID], Countries.[Country ID], Countries.Count ry
FROM Countries


Can I use the following? - SELECT Countries.[Region ID], Countries.[Country ID], Countries.Count ry FROM Countries WHERE Countries.[Region ID] = strIN

Or is it more complex than that?

Thanks.