1. Your list box (call it lstList1) has a Row Source, for example a field called List1 in a table called tblList1Values. So maybe the Row Source property (Properties window, Data tab) looks like
SELECT List1 FROM tblList1Values
To eliminate the duplicates change the Row Source to
SELECT DISTINCT List1 FROM tblList1Values
(You could also add "ORDER BY List1" to sort the list box.)
2. Maybe the second list box (call it lstList2) gets its values from a field called List2 in a table called tblList2Values, so its Row Source is
SELECT List2 FROM tblList2Values
tblList2Values would also have a field called, say, List1Pointer, that relates the second table to the first.
OK, click the Events tab in the Properties window, then click the FIRST list box, then click the After Update row in the Properties window. In the pull-down for that row select [Event Procedure], then click the "..." to the right of the row. A window will open showing you a Visual Basic editing window, and the cursor should be positioned right after a line that reads
Private Sub lstList2_AfterUpdate() (or similar)
and after the blank line with the cursor in it you should see a line that reads
End Sub
What you want to do is change the Row Source for the SECOND list box when the FIRST list box changes. To do this, add the following lines between the Sub and End Sub statements:
Me.lstList2.RowSource = "SELECT DISTINCT List2 FROM tblList2Values " _
& "WHERE List1Pointer = '" & Me.lstList1 & "' ORDER BY List2;"
Me.List2.Requery
This makes the Row Source of the second query change whenever the first list box changes, so the row source for the second list box contains only rows where List1Pointer contains the value in the first list box.
You may have to fool with the delimiter for Me.List1 - I used a single quote, assuming the List1 list box takes alphanumeric values. If the values are numeric, lose the single quotes.
HTH
Jim
