Here’s what I’m trying to do:
On a selection form (frmSelect2) I have a subform (sfrAvailable) and a listbox (lstSelected). The subform is bound to qryAllSeasons which includes a checkbox field called “Select”. When I select the checkbox for a given plant, I want the name of the plant to appear in the listbox. When I deselect it I want the plant to be removed from the listbox. Since the filtering will be accomplished with the subform’s Select field = True, the listbox is just a convenient summary of what has been selected as I scroll through the subform.
The subform AfterUpdate event has this code:
Code: Select all
Private Sub Form_AfterUpdate()
Dim sAddOn As String
Dim oList1 As ListBox
If blnNoUpdate Then Exit Sub
'List of selected items
Set oList1 = [Forms]![frmselect2]![lstSelected]
'Name of current plant
sAddOn = [Forms]![frmselect2]![sfrAvailable]![Plant]
If Not CheckForItem(sAddOn, oList1) Then
'Control 'Select' is a checkbox:
If [Forms]![frmselect2]![sfrAvailable]![Select] Then
[Forms]![frmselect2]![lstSelected].AddItem sAddOn
End If
End If
DoCmd.Requery
End Sub
Code: Select all
Function CheckForItem(strItem, ListB As ListBox) As Boolean
Dim i
CheckForItem = False
CheckForItem = InStr(ListB.RowSource, strItem) > 0
If CheckForItem Then
With ListB
For i = 0 To .ListCount - 1
If .ItemData(i) = strItem Then
If Not RemoveListItem(ListB, i) Then MsgBox strItem & " not removed"
Exit Function
End If
Next i
End With
End If
End Function
Code: Select all
Function RemoveListItem(ctrlListBox As ListBox, _
ByVal varItem As Variant) As Boolean
On Error GoTo ERROR_HANDLER
ctrlListBox.RemoveItem Index:=varItem
RemoveListItem = True
On Error GoTo 0
Exit Function
ERROR_HANDLER:
RemoveListItem = False
End Function