Although there are certainly ways to parse phrases into
discrete words and then search certain tables for those
words, I believe this approach would yield too many
unwanted records.
As an alternative, I would suggest the following:
1) Create a search form containing (unbound) text boxes
that directly correspond to the fields that you wish to
search. If you want to search on a combination of 4
different fields, then your search form should contain 4
different text boxes where the user would enter the search
criteria.
2) Enter any descriptive text in the label for each text
box. This way, the users know what type of information to
enter.
3) Place two command buttons on the form.
a) Label the 1st button as "Display" (or "Search")
and assign the name as "cmdDisplay".
b) Label the 2nd button as "Close" and assign the
name as "cmdClose".
Put the below code in the VBA module of your search form.
=======================================================
Option Compare Database
Option Explicit
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
DoCmd.Close acForm, Me.Name
Exit_cmdClose_Click:
Exit Sub
Err_cmdClose_Click:
If Err.Number <> 2501 Then
MsgBox "Module: " & vbTab & vbTab & Me.Name &
vbCrLf _
& "Procedure #: " & vbTab & "1" & vbCrLf _
& "Error #: " & vbTab & vbTab & Err.Number &
vbCrLf _
& "Description: " & vbTab & Err.Description
Else
Resume Exit_cmdClose_Click
End If
End Sub
Private Sub cmdDisplay_Click()
On Error GoTo Err_cmdDisplay_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim ctl As Control
stDocName = "YourMaintenanceForm" 'This is the main
form you use to view/maintain the data.
stLinkCriteria = ""
'Loop through each text box control on the form to get
its value.
'IMPORTANT: This sub requires that each control used
in the search functionality
'be assigned the SAME NAME as the field in the table.
This is because the below
'BuildCriteria function constructs the string using
the control name as part of the
'string that that will be used in the where clause
(stLinkCriteria) in the Open
'Form statement.
For Each ctl In Me.Controls
With ctl
Select Case .ControlType
Case acTextBox
.SetFocus
'Field must have focus to obtain .Text
value.
If stLinkCriteria = "" And Not IsNull
(ctl.Value) Then
stLinkCriteria = BuildCriteria
(.Name, vbString, .Text)
ElseIf Not IsNull(ctl.Value) Then
stLinkCriteria = stLinkCriteria
& " And " & BuildCriteria(.Name, vbString, .Text)
End If
End Select
End With
Next ctl
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria
DoCmd.Close acForm, Me.Name
Exit_cmdDisplay_Click:
Exit Sub
Err_cmdDisplay_Click:
If Err.Number <> 2501 Then
MsgBox "Module: " & vbTab & vbTab & Me.Name &
vbCrLf _
& "Procedure #: " & vbTab & "2" & vbCrLf _
& "Error #: " & vbTab & vbTab & Err.Number &
vbCrLf _
& "Description: " & vbTab & Err.Description
Else
Resume Exit_cmdDisplay_Click
End If
End Sub
=======================================================
