search for multiple records in a form

  • Thread starter Thread starter Pablo
  • Start date Start date
P

Pablo

I have a form that queries a table based on multiple filters, such as Part
Number, Part Type, etc. Currently the search code is enabled to search for
one part number entered in a text box, and the search results are displayed
in a subform.

I want to be able to enhance my search functionality by allowing multiple
part numbers to be entered into the Part Number search field, and displaying
the results in the subform for the records it matches. Please help! Here's
the specific code for the search form:

Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"


' If Title
If Nz(Me.Title) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Issues.[Part Number] Like '*" &
Me.Title & "*'"
End If



If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Browse Issues", acFormDS, , strWhere, acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Browse_All_Issues.Form.Filter = strWhere
Me.Browse_All_Issues.Form.FilterOn = True
End If
End Sub
 
Hi Allen,

Good morning from America! You database works, but I was wondering if there
is also a way to search by entering a list of values in order to get my
results.

For example, from your database, I want to be able to enter "Smith",
"Jones", and "Shell Oil" in the Name Contains box in order to get all those
records.

The ideal would be for me to be able to copy and paste a list into the
search box...

Thanks,

Pablo

Allen Browne said:
Pablo, take a look at this example:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Pablo said:
I have a form that queries a table based on multiple filters, such as Part
Number, Part Type, etc. Currently the search code is enabled to search
for
one part number entered in a text box, and the search results are
displayed
in a subform.

I want to be able to enhance my search functionality by allowing multiple
part numbers to be entered into the Part Number search field, and
displaying
the results in the subform for the records it matches. Please help!
Here's
the specific code for the search form:

Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"


' If Title
If Nz(Me.Title) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Issues.[Part Number] Like '*" &
Me.Title & "*'"
End If



If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Browse Issues", acFormDS, , strWhere, acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Browse_All_Issues.Form.Filter = strWhere
Me.Browse_All_Issues.Form.FilterOn = True
End If
End Sub
Click to expand...
Click to expand...
 
You will need some programming experience to achieve that.

Choose a delimiter between values (looks like comma in your example.)

Use Split() to parse the list into an array.

Loop through the array, concatenating the values together into a long string
with value around each value and commas between them.

Then add the field name, IN operator, and brackets around the string.

In the end you will end up with a string that looks like the WHERE clause in
a query. Example:
"[ClientName] IN (""Smith"", ""Jones"", ""Shell Oil"")"

You can then use that as the Filter for your form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Pablo said:
Hi Allen,

Good morning from America! You database works, but I was wondering if
there
is also a way to search by entering a list of values in order to get my
results.

For example, from your database, I want to be able to enter "Smith",
"Jones", and "Shell Oil" in the Name Contains box in order to get all
those
records.

The ideal would be for me to be able to copy and paste a list into the
search box...

Thanks,

Pablo

Allen Browne said:
Pablo, take a look at this example:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
Pablo said:
I have a form that queries a table based on multiple filters, such as
Part
Number, Part Type, etc. Currently the search code is enabled to search
for
one part number entered in a text box, and the search results are
displayed
in a subform.

I want to be able to enhance my search functionality by allowing
multiple
part numbers to be entered into the Part Number search field, and
displaying
the results in the subform for the records it matches. Please help!
Here's
the specific code for the search form:

Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid
date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"


' If Title
If Nz(Me.Title) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Issues.[Part Number] Like '*" &
Me.Title & "*'"
End If



If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Browse Issues", acFormDS, , strWhere,
acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight +
Me.FormFooter.Height
End If
Me.Browse_All_Issues.Form.Filter = strWhere
Me.Browse_All_Issues.Form.FilterOn = True
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Allen,
I also tried using your search form database. However, I'm running into an
issue of it only searching on the first field. Both the State and County
fields are text fields. Here is my code:
**********
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long


If Not IsNull(Me.cboState) Then
strWhere = strWhere & "([State] = """ & Me.cboState & """) AND "
End If

If Not IsNull(Me.lstCounty) Then
strWhere = strWhere & "([County] = """ & Me.lstCounty & """) AND "
End If


lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
 
Back
Top