Firstly don't name an unbound control with the name of a column (field) in
the form's underlying table; call it something like txtFindOrderNumber.
I'm not clear whether you want to search for multiple order numbers or on
the basis of separate columns in the table. If the latter do you want to
return rows which match both values or whre either values match? So I'll
deal with al three scenarios:
1. To find multiple order numbers I'd suggest using a multi-select list
box; you can then select as few or as many as you wish. Set its MultiSelect
property to either Simple or Extended; the former enables multiple selections
by simply clicking each in turn, the latter allows the use of Ctrl+Click or
Shift+Click in the usual way to select ranges. Set its RowSource so it lists
all the values from the column in question, sorting them either numerically
or alphabetically, e.g.
SELECT [OrderNum] FROM [Orders] ORDER BY [OrderNum];
To execute the search use your Find button, with code in its Click event
procedure which iterates through the selected items in the list box and sets
the forms Filter property, e.g.
Dim varItem As Variant
Dim strOrderNumbersList As String
Dim ctrl As Control
Set ctrl = Me.lstFindOrderNumber
If ctrl.ItemsSelected.Count > 0 Then
' build value list of selected items
For Each varItem In ctrl.ItemsSelected
strOrderNumbersList = stOrderNumbersList & "," &
ctrl.ItemData(varItem)
Next varItem
' remove leading comma from value list
strOrderNumbersList = Mid(strOrderNumbersList, 2)
' filter form
MeFilter = "[OrderNum] In(" & strOrderNumbersList & ")"
Me.FilterOn = True
Else
MsgBox "No orders selected.", vbExcalamation, "Invalid Operation"
End If
Where lstFindOrderNumber is the name of the list box. The above assumes
that the order numbers are a number data type, not text. With a text data
type you'd amend it slightly:
strOrderNumbersList = stOrderNumbersList & ",""" &
ctrl.ItemData(varItem) & """"
To clear the selections and show all records you can add another button to
the form with the following in its Click event procedure:
Dim n As Integer
' clear all selections from list box
For n = 0 To Me.lstFindOrderNumber .ListCount - 1
Me.lstStkNumbers.Selected(n) = False
Next n
' turn filter off
Me.FilterOn = False
2. To search on different columns in the table lets assume you want to
search by OrderNum and by Product and return rows which match *both*, the
first being a number data type the second text, and you want each of these to
be optional, i.e. the user can search on either or both of them. Say the
text boxes are called txtFindOrderNumber and txtFindProduct, the button's
code would go like this:
Dim strFilter As String
' has user entered an order number?
If Not IsNull(Me.txtFindOrderNumber) Then
strFilter = strFilter & " And [OrderNum] = " & MetxtFindOrderNumber
End If
' has user entered a product?
If Not IsNull(Me.txtFindProduct) Then
strFilter = strFilter & " And [Product] = """ & MetxtFindProduct &
""""
End If
' if neither text box has value do nothing
If Len(strFilter) > 0 Then
' remove leading ' And '
strFilter = Mid(strFilter,6)
' filter form
Me.Filter = strFilter
Me.FilterOn = True
End If
3. To search on different columns and return rows which match *either* the
button's code would go like this:
Dim strFilter As String
' has user entered an order number?
If Not IsNull(Me.txtFindOrderNumber) Then
strFilter = strFilter & " Or [OrderNum] = " & MetxtFindOrderNumber
End If
' has user entered a product?
If Not IsNull(Me.txtFindProduct) Then
strFilter = strFilter & " Or [Product] = """ & MetxtFindProduct &
""""
End If
' if neither text box has value do nothing
If Len(strFilter) > 0 Then
' remove leading ' Or '
strFilter = Mid(strFilter,5)
' filter form
Me.Filter = strFilter
Me.FilterOn = True
End If
As you'll see 2 is a Boolean AND operation, 3 is a Boolean OR operation.
For 2 and 3 to show all record s you can add another button with the
following in its Click event procedure:
' turn off filter
Me.FilterOn = False
Ken Sheridan
Stafford, England
