Tags: abutton, access, create, creating, database, enters, example, form, includes, microsoft, mysql, oracle, search, sql, textbox, tosearch, user, words
How to create a Search form
On Database » Microsoft Access
5,518 words with 4 Comments; publish: Fri, 06 Jun 2008 15:02:00 GMT; (25078.13, « »)
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
http://ms-access.itags.org/q_ms-access-database_96385.html
All Comments
Leave a comment...
- 4 Comments
- 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
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Thomas" <ThominoS.ms-access.itags.org.gmail.com> wrote in message
news:1137586523.664571.175920.ms-access.itags.org.g47g2000cwa.googlegr oups.com...
> 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
#1; Fri, 06 Jun 2008 15:03:00 GMT
- "Thomas" <ThominoS.ms-access.itags.org.gmail.com> wrote in message
news:1137586523.664571.175920.ms-access.itags.org.g47g2000cwa.googlegr oups.com...
> 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.
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
#2; Fri, 06 Jun 2008 15:04:00 GMT
- 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
#3; Fri, 06 Jun 2008 15:05:00 GMT
- 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
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Thomas" <ThominoS.ms-access.itags.org.gmail.com> wrote in message
news:1137589285.900810.62050.ms-access.itags.org.z14g2000cwz.googlegro ups.com...
> 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.
#4; Fri, 06 Jun 2008 15:06:00 GMT