How to create a Search form

  • Thread starter Thread starter Thomas
  • Start date Start date
T

Thomas

Hi,
I need a help with creating a search form, which includes textbox and a
button. If user enters for example 4 words, I want the database to
search in a field of a table for these 4 words in random order. For
example: If user writes "512 Apac", search results will contain "Apacer
DDRAM 512MB" and so on. I am desperate with this problem, so I welcome
any working solution.

Thanks a lot.
Thomas
 
So you want to have an unbound text box where the user can enter any words
in any order, and then filter the form to show any records that have any of
these words in (say) the field called Notes.

The example below shows how to parse the words in the text box, and build a
Filter string for your form. The code goes into the AfterUpdate event
procedure of the unbound text box named txtKeywords in this example.
Requires Access 2000 or later.

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub
 
Thomas said:
Hi,
I need a help with creating a search form, which includes textbox and a
button. If user enters for example 4 words, I want the database to
search in a field of a table for these 4 words in random order. For
example: If user writes "512 Apac", search results will contain "Apacer
DDRAM 512MB" and so on. I am desperate with this problem, so I welcome
any working solution.
Click to expand...

It would be a difficult task to parse out all 4 words in any order to do the
search. It is quite easy to use 4 separate text boxes and then use a query
like:

SELECT DISTINCTROW ID, SomeTextOrMemoField FROM MyTable WHERE
(((SomeTextOrMemoField) Like "*" & [Forms]![frmSearch]![Find1] & "*" And
(SomeTextOrMemoField) Like "*" & [Forms]![frmSearch]![Find2] & "*" And
(SomeTextOrMemoField) Like "*" & [Forms]![frmSearch]![Find3] & "*" And
(SomeTextOrMemoField) Like "*" & [Forms]![frmSearch]![Find4] & "*"));

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
Thanks Allen, I understood your solution, but there is a bit more I
need to do. In my form I have a list box, which I want to update with
the filter. I want to display only rows that contains search words. I
will be very thankfull for any suggestion.

Thomas
 
So you want to select the words in a multi-select list box instead of typing
them in a form?

That's just a matter of looping through the ItemsSelected collection, to
form the Filter string. The logic and coding is the same as:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
 
Back
Top