Updating selected items from List Mover to table (1 Viewer)

Option Compare Database
Option Explicit

Private Sub cmdAdd_Click()

Dim in_clause As String: in_clause = ""
Dim strSQL As String, n 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 Vocabulary" _
           & " WHERE VocabID IN (" & in_clause & ")"

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

End Sub

Private Sub cmdAssign_Click()

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

With Me.lfmVocabularyAssign
    For n = 0 To .ListCount - 1
       If .Selected(n) = True Then
       DoCmd.RunSQL "UPDATE VocabularyFocus Set VocabIDfk = '"
       
       

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, 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
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 Form_Load()

    Me.lfmVocabularyAssign.RowSource = ""
    Me.lfmVocabulary.RowSource = "Vocabulary"
    Me.lfmVocabulary.RowSourceType = "Table/Query"
    Me.lfmVocabulary.Requery

End Sub

Private Sub txtSearchBox_Change()

    Dim strWhere As String
        With Me.txtSearchBox
          If .Text = vbNullString Then
          strWhere = "(False)"
        Else
          strWhere = "Vocabulary Like """ & .Text & "*"""
        End If
        End With
        
        With Me.lfmVocabulary
        .Filter = strWhere
        .FilterOn = True
        End With

End Sub

Private Sub cmdAssign_Click()

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

With Me.lfmVocabularyAssign
    For n = 0 To .ListCount - 1
       If .Selected(n) = True Then
            [COLOR="DarkGreen"]DoCmd.RunSQL "UPDATE VocabularyFocus Set VocabIDfk = '"[/COLOR]
        [COLOR="Red"]End If
    Next n
End With[/COLOR]   

End Sub

DoCmd.RunSQL "UPDATE VocabularyFocus Set VocabIDfk =[COLOR="red"]''[/COLOR]"

Private Sub cmdAssign_Click()

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

With Me.lfmVocabularyAssign
    For n = 0 To .ListCount - 1
       If .Selected(n) = True Then
            DoCmd.RunSQL "UPDATE VocabularyFocus Set VocabIDfk = '"
        End If
    Next n
End With   

End Sub