Filter a Form based on a List Box
I have come across coding for filtering a report based on a list box, but I have had no success locating any code for filtering a form based on a list box. I would like a seperate form to open based on the selected item from the list box. Does anyone have an idea how this could be accomplished? Any help is always appreciated. Thank you in advance.
Code:
Private Sub CmdRpt_Click()
'Run a report displaying only the records chosen
'by the user in the form's listbox.
Dim v As Variant
Dim Frm As Form
Dim ctl As Control
Dim theId As Long
Dim WhereCrit As String
'If nothing is selected, notify user...
If Me.LstFindings.ItemsSelected.Count = 0 Then
MsgBox "Please select a supplier or two.", vbExclamation, "No Supplier Selected"
Exit Sub
End If
'Assign form and control to object variables.
Set Frm = Forms!FrmSampleList
Set ctl = Frm!LstFindings
'Begin building Where string.
WhereCrit = "SupplierID = "
'Add each selected item to the WHERE string.
For Each v In ctl.ItemsSelected
'Coordinates: 1st column (0); row v
'where v changes for each round of the loop.
theId = ctl.Column(0, v)
'Tag on to string.
WhereCrit = WhereCrit & theId & " OR SupplierID = "
Next v
'Loop ends; selected items are now accounted for...
'Clean-up the Where string by removing the trailing text.
WhereCrit = Left(WhereCrit, Len(WhereCrit) - 17)
'By default, the Suppliers Report returns all records from the Suppliers Table.
'Here the Suppliers Report is opened using the 'Where clause' to filter it down _
to only the items selected in the listbox.
DoCmd.OpenReport "RptSuppliers", acViewPreview, , WhereCrit
End Sub
