Listbox function

[NYbills]

I have a form based off a parameter query. Once you open the form it prompts
you for the parameters. I am okay with this function. This just simply
limits the data by month/year. Inside this form I would like to incorporate
a list box that will upon selecting the listbox(product family) will requery
the results and show only the records related to the list box selections. I
created the following list box and below is the code. This simply takes you
to the record. I only want to show the records related to the listbox
selection.

Some options would be like to have to is multiple select in the listbox as
well. Any ideas on how to produce such a form? Your feedback is greatly
appreciated.

Thanks.


Private Sub List206_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Product Family] = '" & Me![List206] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Ken Sheridan]

With a multi-select list box you need to iterate through its ItemsSelected
collection and build a string expression which can be used to filter the form:

Dim varItem As Variant
Dim strProductFamilyList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me. List206
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strProductFamilyList = strProductFamilyList & _
",""" & ctrl.ItemData(varItem) & """"
Next varItem

' remove leading comma
strProductFamilyList = Mid(strProductFamilyList, 2)

strCriteria = "[Product Family] In(" & strProductFamilyList & ")"

Me.Filter = strCriteria
Me.FilterOn = True
Else
' if no items selected then turn off filter
Me.FilterOn = False
End If

Ken Sheridan
Stafford, England