MS Access multi-select list mover v2

Question

As an extension to the previous post listed below:

Ms Access multi-select listbox mover

I received great help from some of the community members here (Shout out to @Parfait for his continued assistance and @Erik Von Asmuth) in regards to a problem I was experiencing in coding a multi-select list mover.

The first problem has been resolved, however, I am experiencing a new problem in the excution on cmdRemove_Click() button. The problem is as follows:

I have two list boxes (lfmVocabulary and lfmVocabularyAssign). The first list box (lfmVocabulary) has a multi-select function to choose vocabulary words to be assigned to a unit, thus transferred into the lfmVocabularyAssign list box. I can select and transfer the .selected with no problems (thanks to the community), now I have a problem sending them back from the lfmVocabularyAssign list box to the lfmVocabulary.

Say I have Vocabulary words A, B, and C. I select A & B to be transferred, hit cmdAdd and no problems. But if I select A & B and hit cmdRemove to transfer them back to the first list box, C takes the place of A & B in the second list box!

Here is my code:

Option Compare Database


Private Sub cmdAdd_Click()

Dim in_clause As String: in_clause = ""
Dim strSQL As String, i As Integer

' ITERATE TO BUILD COMMA-SEPARATED LIST FOR SQL IN() CLAUSE
With Me.lfmVocabulary
    For n = 0 To .ListCount - 1
       If .Selected(n) = True Then
           in_clause = in_clause & .ItemData(n) & ", "
       End If
    Next n
End With

' REMOVE LAST COMMA AND SPACE
in_clause = Left(in_clause, Len(in_clause) - 2)

strSQL = "SELECT * FROM qryVocabularyDefinitions" _
           & " WHERE VocabSpeechDefID IN (" & in_clause & ")"

Me.lfmVocabularyAssign.RowSource = strSQL
Me.lfmVocabularyAssign.RowSourceType = "Table/Query"
Me.lfmVocabularyAssign.Requery

End Sub

Private Sub cmdClearAll1_Click()

 Dim n As Integer

    With Me.lfmVocabulary
        For n = 0 To .ListCount - 1
            .Selected(n) = False
        Next n
    End With

End Sub

Private Sub cmdClearAll2_Click()

 Dim n As Integer

    With Me.lfmVocabularyAssign
        For n = 0 To .ListCount - 1
            .Selected(n) = False
        Next n
    End With

End Sub

Private Sub cmdRemove_Click()

Dim in_clause As String: in_clause = ""
Dim strSQL As String, i As Integer

' ITERATE TO BUILD COMMA-SEPARATED LIST FOR SQL IN() CLAUSE
With Me.lfmVocabularyAssign
    For n = 0 To .ListCount - 1
       If .Selected(n) = True Then
           in_clause = in_clause & .ItemData(n) & ", "
       End If
    Next n
End With

' REMOVE LAST COMMA AND SPACE
in_clause = Left(in_clause, Len(in_clause) - 2)

strSQL = "SELECT * FROM qryVocabularyDefinitions" _
           & " WHERE VocabSpeechDefID NOT IN (" & in_clause & ")"

Me.lfmVocabularyAssign.RowSource = strSQL
Me.lfmVocabularyAssign.RowSourceType = "Table/Query"
Me.lfmVocabularyAssign.Requery

End Sub

Private Sub cmdSelectAll1_Click()

 Dim n As Integer

    With Me.lfmVocabulary
        For n = 0 To .ListCount - 1
            .Selected(n) = True
        Next n
    End With

End Sub


Private Sub cmdSelectAll2_Click()
 Dim n As Integer

    With Me.lfmVocabularyAssign
        For n = 0 To .ListCount - 1
            .Selected(n) = True
        Next n
    End With
End Sub

Private Sub cmdAssign_Click()

:(

End Sub

Private Sub Form_Load()

    Me.lfmVocabulary.RowSource = "qryVocabularyDefinitions"
    Me.lfmVocabulary.RowSourceType = "Table/Query"
    Me.lfmVocabulary.Requery

End Sub 

Answer 1

Well, you're running into an obvious problem here, because @Parfait his solution to your initial problem made it harder to move items back and forth, because they are pulled in using queries. I am assuming that with his solution items aren't removed from the first list, only added to the second. If so, this should work:

Private Sub cmdRemove_Click()

Dim in_clause As String: in_clause = ""
Dim strSQL As String, n As Integer
'Set the SQL to the current SQL
strSQL = Me.lfmVocabularyAssign.RowSource


' ITERATE TO REMOVE ITEMS FROM COMMA-SEPARATED LIST FOR SQL IN() CLAUSE
With Me.lfmVocabularyAssign
    For n = 0 To .ListCount - 1
       If .Selected(n) = True Then
           If InStr(1, strSQL, ", " & .ItemData(n) ) <> 0 Then
                'Not the first item, nor the only item
                strSQL = Replace(strSQL, ", " & .ItemData(n), "")
           ElseIf InStr(1, strSQL, .ItemData(n) & ", " ) <> 0 Then
                'It's the first item
                strSQL = Replace(strSQL, .ItemData(n) & ", ", "")
            Else
                'It's the only item
                strSQL = Replace(strSQL, .ItemData(n), "")
            End If
       End If
    Next n
End With


Me.lfmVocabularyAssign.RowSource = strSQL
Me.lfmVocabularyAssign.RowSourceType = "Table/Query"
Me.lfmVocabularyAssign.Requery

End Sub