Help with advanced search capabilty... 1

[petrosky]

Hi,

I am trying to emulate a native PICK application's product search system...(long story.)

Anyway, I have no problem assigning my search forms recordsource to the value of a TextBox but I want the users to be able to "drill down" the search options.

eg. I have the following.

On Error GoTo Err_Trap
Dim strSearch As String

strSearch = Me.SearchMe

Forms![AllProducts].Visible = True

Forms![AllProducts].RecordSource = "SELECT * FROM d3_import WHERE" & _
"(((d3_import.[rsv_desc])Like """ & "*" & strSearch & "*" & """))" & _
"ORDER BY d3_import.[rsv_desc] ASC;"

Err_Trap:
If Err.Number <> 0 Then
MsgBox Err.Number & " " & Err.Description
End If

End Sub

What I would like to be able to do is re-use the textbox variable against the recordsource created here.

Eg. User enters the string

CANON

The form would display all records with canon in the description. eg.

CANON EOS 450D BODY
CANON EOS 400D BODY
CANON EOS 1DS BODY

Now, if a user typed in

450D

it would drill down to the only option available.

This "drilling down" may have more iterations than the example sited.

Does anyone have any idea how I can attack this one?

Thanks in advance.

Peter.




Remember- It's nice to be important,
but it's important to be nice

 

[PHV]

You may play with the Filter and FilterOn properties of the form.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

Here is a rough idea using the Northwind database and the Products table.

public strFilter As String

Private Sub cmdClearFilter_Click()
  strFilter = ""
  Me.FilterOn = False
  Me.txtBxSearch = ""
End Sub

Private Sub cmdSearch_Click()
   If Not txtBxSearch = "" Then
     If strFilter = "" Then
        strFilter = " ProductName Like '*" & txtBxSearch & "*'"
     Else
        strFilter = strFilter & " AND ProductName Like '*" & txtBxSearch & "*'"
     End If
     MsgBox strFilter
     Me.Filter = strFilter
     Me.FilterOn = True
   End If
   Me.txtBxSearch = ""
End Sub

 

[petrosky]

MajP,

What an elegant solution. A star for you.

Regards,

Peter.



Remember- It's nice to be important,
but it's important to be nice

 

[SeeThru]

Make sure the searched table is local, or it can get really S L O W .. ... . . . .

SeeThru
Synergy Connections Ltd - Telemarketing Services

http://www.synergyconnections.co.uk

 

[petrosky]

Hi SeeThru,

The table resides on a MYSQL server on our lan. The table contains only about 15000 product records. The filteron method works very well in our situation.

Regards,

Peter.

Remember- It's nice to be important,
but it's important to be nice

 

[1VBAnewbie]

To add to what Petrosky needed assistance with, I would like my users to be able to enter multiple keywords (i.e., Canon, Nikon, Sony) and have all records with any of these values in the Keywords column returned.

How would the solution already provided be modified so this would occur? Keep in mind that only one or more than one entered value may be in a Keywords column.

 

[MajP]

This works if they seperate all keywords with a ",". You would probably need a lot of error checking, but this is a start.

Private Sub cmdKeyWord_Click()
  Dim strFilter As String
  Dim aSearch() As String
  Dim intCounter As Integer
  Dim var As Variant
  aSearch = Split(Me.txtBxKeyWord, ",")
  For intCounter = LBound(aSearch) To UBound(aSearch)
    MsgBox aSearch(intCounter)
    If intCounter = LBound(aSearch) Then
        strFilter = " ProductName Like '*" & Trim(aSearch(intCounter)) & "*'"
     Else
        strFilter = strFilter & " OR ProductName Like '*" & Trim(aSearch(intCounter)) & "*'"
     End If
  Next intCounter
  MsgBox strFilter
  Me.Filter = strFilter
  Me.FilterOn = True
  Me.txtBxKeyWord = ""
End Sub

 

[1VBAnewbie]

Works great. thanks.