Immediately update listbox with subform selection

[Jeff H]

So now I’m converting the Garden spreadsheet you previously helped me with in Excel, to Access. The current issue is that I want to select plants to review on a form or a report. I almost have it working. The problem is, the listbox does not update until I move to the next record in the subform (presumably because I’m using the subform AfterUpdate event?). I’d like to have the plant name appear in the listbox as soon as the checkbox is selected and be removed as soon as it's deselected.

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

The following two functions do the work:

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

And

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

[HansV]

You should use the After Update event of the check box on the subform.

[Jeff H]

That makes good sense! And it works just the way I want, except that it only adds one item to the listbox. Is there something like a Preserve attribute I can apply that will allow the listbox to accumulate all the selected items?

[Jeff H]

OOPS!!! My mistake. I forgot to rem out the Form_AfterUpdate when I tested it!
This works perfectly.

Thanks!