Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Use multiselect listbox without temp tables or SQL updates

Using list boxes for Criteria

Use multiselect listbox without temp tables or SQL updates

by  dhookom  Posted    (Edited  )
You can create a function in a standard module as noted below. This function can then be used in a query to filter out values not selected in the list box. If no items are selected, the query will return all records.

The way you might implement this with the Employee table in Northwind with a form "frmMultiselectList" and a listbox "lboEmployeeID" uses this SQL:
Code:
SELECT Employees.*
FROM Employees
WHERE IsSelectedVar("frmMultiselectList","lboEmployeeID",[EmployeeID])=-1;

This function may not perform very well with very large tables.

Code:
Function IsSelectedVar( _
        strFormName As String, _
        strListBoxName As String, _
        varValue As Variant) _
            As Boolean
    'strFormName is the name of the form
    'strListBoxName is the name of the listbox
    'varValue is the field to check against the listbox
    Dim lbo As ListBox
    Dim item As Variant
    If IsNumeric(varValue) Then
        varValue = Trim(Str(varValue))
    End If
    Set lbo = Forms(strFormName)(strListBoxName)
    If lbo.ItemsSelected.Count = 0 Then
        IsSelectedVar = True 'return all if no items selected
      Else
        For Each item In lbo.ItemsSelected
            If lbo.ItemData(item) = varValue Then
                IsSelectedVar = True
                Exit Function
            End If
        Next
    End If
End Function

There is a sample MDB of this at http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
.
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top