Listbox Requery (1 Viewer)

Dim varItem as Variant

For Each varItem in lstProducts.ItemsSelected

lstProducts.Selected(varItem) = False

Next varItem

lstProducts.Requery

Private Sub Form_Current()
 ' The On Current event. Clear the selections then hilite the selections."
  ClearSelections Me.lstProducts
  If Not Me.NewRecord Then
    ShowSelections Me.lstProducts, Me.OrderID
  End If
End Sub
Public Sub ShowSelections(lst As Access.ListBox, ForeignKey As Long)
  Dim RS As DAO.Recordset
  Dim strSql As String
  Dim varItm As Variant
  Dim SelectedID As Long
  Dim i As Integer
  'Need to get a recordset of the items to hilite in the listbox
  strSql = "Select ProductID from qryOrders_Products where OrderID = " & ForeignKey
  Set RS = CurrentDb.OpenRecordset(strSql)
  'loop the recordset
  Do While Not RS.EOF
    SelectedID = RS!ProductID
    'loop the items in the listbox to find a match
    For i = 0 To (lst.ListCount - 1)
      Debug.Print lst.Column(0, i) & " " & SelectedID
      If lst.Column(0, i) = SelectedID Then
        lst.Selected(i) = True
        Exit For
      End If
    Next i
    RS.MoveNext
  Loop
End Sub
Public Sub ClearSelections(lst As Access.ListBox)
  Dim i As Integer
  For i = 0 To (lst.ListCount - 1)
     lst.Selected(i) = False
   Next i
End Sub

Private Sub lstProducts_AfterUpdate()
  If Not IsNull(Me.OrderID) Then
    AddRemoveSelections Me.OrderID
    Me.SubFrmOrderProducts.Form.Requery
  Else
    MsgBox "Create the order first by giving it an order date."
  End If
End Sub
Public Sub AddRemoveSelections(OrderID As Long)
  Dim lst As Access.ListBox
  Dim RS As DAO.Recordset
  Dim FocusedItemIndex As Integer
  Dim strSql As String
  Dim ProductID As Long
  Set lst = Me.lstProducts
  FocusedItemIndex = lst.ListIndex
  ProductID = lst.Column(0, FocusedItemIndex)
  'The selected property is an array of all items and holds a true false if the item is selected
   If lst.Selected(FocusedItemIndex) Then
     'If the item is focused and selected then add it.Add to list
     strSql = "Insert INTO tblOrder_Products (OrderID, ProductID) VALUES (" & OrderID & ", " & ProductID & ")"
   Else
     'If the item is focused but not selected then delete it
     strSql = "DELETE * FROM tblOrder_Products where OrderID = " & OrderID & " AND ProductID = " & ProductID
   End If
   Debug.Print strSql
   CurrentDb.Execute strSql
End Sub