ListBox Comparison (1 Viewer)

Public Sub ClearList(lst As Access.ListBox)
  Dim lstItem As Integer
  For lstItem = lst.ListCount - 1 To 0 Step -1
    lst.RemoveItem (lstItem)
    'Debug.Print "remove"
  Next lstItem
End Sub

Public Sub MoveFromListToList(SourceList As ListBox, DestinationList As ListBox)
  Dim varItem As Variant
  Dim strAdd As String
  Dim columnCount As Integer
  Dim i As Integer
  columnCount = SourceList.columnCount
  For Each varItem In SourceList.ItemsSelected
     For i = 0 To columnCount - 1
        If strAdd = "" Then
           strAdd = SourceList.Column(i, varItem)
        Else
           strAdd = strAdd & ";" & SourceList.Column(i, varItem)
        End If
     Next i
      DestinationList.RowSourceType = "value list"
      DestinationList.AddItem strAdd
      strAdd = ""
  Next varItem
  'remove from source
  RemoveFromList SourceList
End Sub

Public Sub RemoveFromList(SourceList As ListBox)
  Dim i As Integer
  Dim tempCol As New Collection
  Dim varitm As Variant
  'once you delete an item it clears the itemselected
  'so you need to store these values
  'must remove in descending order
  For Each varitm In SourceList.ItemsSelected
    tempCol.Add (varitm)
    If SourceList.Name = Me.ListFrom.Name Then
      'Remove from From list adding to To list
      RaiseEvent ItemToAdded(SourceList.Column(Me.BoundColumn, varitm))
    Else
      RaiseEvent ItemFromAdded(SourceList.Column(Me.BoundColumn, varitm))
    End If
  Next
  For i = tempCol.Count To 1 Step -1
    SourceList.RemoveItem (tempCol(i))
  Next i
End Sub
Public Sub MoveAll(SourceList As ListBox, DestinationList As ListBox)
  Dim lstItem As Integer
  
  For lstItem = 0 To SourceList.ListCount - 1
    SourceList.Selected(lstItem) = True
  Next lstItem
  
  MoveFromListToList SourceList, DestinationList
End Sub

Public Sub ClearList(lst As Access.ListBox)
  Dim lstItem As Integer
  For lstItem = lst.ListCount - 1 To 0 Step -1
    lst.RemoveItem (lstItem)
    'Debug.Print "remove"
  Next lstItem
End Sub

Public Sub LoadFrom()
  Dim rs As DAO.Recordset
  Dim strAdd As String
  Dim fld As DAO.Field
  ClearList Me.ListFrom
  Set rs = CurrentDb.OpenRecordset(Me.From_SQL)
   Do While Not rs.EOF
   strAdd = ""
   For Each fld In rs.Fields
     If strAdd = "" Then
       strAdd = rs.Fields(fld.Name).Value
     Else
       strAdd = strAdd & ";" & rs.Fields(fld.Name).Value
     End If
   Next fld
   Me.ListFrom.AddItem strAdd
   rs.MoveNext
  Loop

End Sub

Public Sub LoadTo()
  Dim rs As DAO.Recordset
  Dim strAdd As String
  Dim fld As DAO.Field
  ClearList Me.ListTo
  Set rs = CurrentDb.OpenRecordset(Me.To_SQL)
   Do While Not rs.EOF
   strAdd = ""
   For Each fld In rs.Fields
     If strAdd = "" Then
       strAdd = rs.Fields(fld.Name).Value
     Else
       strAdd = strAdd & ";" & rs.Fields(fld.Name).Value
     End If
   Next fld
   Me.ListTo.AddItem strAdd
   rs.MoveNext
  Loop
End Sub

Public Sub LoadLists()
  LoadTo
  LoadFrom
End Sub

Public Function GetListItems(SourceList As ListBox) As Collection
  Dim lstItem As Integer
  Set GetListItems = New Collection
  For lstItem = 0 To SourceList.ListCount - 1
    GetListItems.Add (SourceList.Column(Me.BoundColumn, lstItem))
  Next lstItem
End Function

Public WithEvents FormFromTo As Form_FromToList
Private Sub Form_Load()
  Set FormFromTo = Me.frmFromToList.Form
  Dim FromSql As String
  Dim ToSql As String
  FromSql = "some sql string"
  ToSql = "Some other sql string or optional if not starting with selected items"
  'Initialize the class
  FormFromTo.InitializeFromTo FromSql, ToSql, "Employees Not Selected", "Employees Selected"
End Sub

'Trap the custom event and do something
Private Sub FormFromTo_ItemFromAdded(Item As Variant)
  Dim strSql As String
   'Now run delete query
  strSql = "Delete * from tblSelectedEmployees where SelectedemployeeID = " & Item
  CurrentDb.Execute strSql
  Me.subfrmSelectedEmployees.Form.Requery
End Sub

Private Sub FormFromTo_ItemToAdded(Item As Variant)
  Dim strSql As String
   'Now run insert query
  strSql = "Insert into tblSelectedEmployees (SelectedEmployeeID) Values (" & Item & ")"
  CurrentDb.Execute strSql
  Me.subfrmSelectedEmployees.Form.Requery
End Sub

Does not need to delete records everytime (thereby preventing bloat on the db).