Dynamic Search Box using 'Like'

[elliott_shafii]

Sorry.

You explained that you wanted to enter "an" and not just get andy... you wanted to find the specifed value in the whole word and not just from the start of if.

I want to enter a keyword, and any fields with the keyword in it I want to be brought through into my list box.


Did you managed to figure out how to do a search like this?

hope this make more sense

 

[pbaldy]

I'm not the one who asked the original question. Needing to search multiple fields is a warning sign of a normalization problem, but the way to apply this method to search them is to add OR criteria to your SQL. Using the SQL from this thread:

"...WHERE ContactName LIKE '*" & Me.PreContactSearch.Text & "*' OR OtherField LIKE '*" & Me.PreContactSearch.Text & "*'"

 

[LPurvis]

Will search upon whatever is entered.... within reason. ;-)
i.e. if you search on text containing a double quote it'll fail.
And if you're thinking that's unlikely...

WHERE PlankDescription = ""*" & [Criteria] & "*"";"

Guess what would cause a problem there. ;-)
2"x4"
BOOM! :-D

Replace is the only way to be sure.
Whatever delimiter your choose (single or double) - validating the value to ensure correct parsing is the way.

"WHERE tblContacts.ContactName LIKE '*" & Replace(Me.PreContactSearch.Text, "'", "''") & "*' ORDER BY tblContacts.ContactName;"

or

"WHERE tblContacts.ContactName LIKE ""*" & Replace(Me.PreContactSearch.Text, """", """""") & "*"" ORDER BY tblContacts.ContactName;"

 

[elliott_shafii]

This looks great.

I'll test it when i'm back in, in the morning.

i'll let you know how i get on.

kind regards

Elliott

 

[geraldcor]

Notice my previous post. The reason mine wasn't working was because I was using the wrong delimiters/escape characters. I think the others have answered your question otherwise, but if you want, I can post my exact code and you can tweak it to suit. I have it so that the list displays everything if the presearch box isnull and then as characters are entered it narrows the results. (And yes it searches for the character anywhere in the word - an = andy and panda)

Greg

 

[elliott_shafii]

Notice my previous post. The reason mine wasn't working was because I was using the wrong delimiters/escape characters. I think the others have answered your question otherwise, but if you want, I can post my exact code and you can tweak it to suit. I have it so that the list displays everything if the presearch box isnull and then as characters are entered it narrows the results. (And yes it searches for the character anywhere in the word - an = andy and panda)

Greg

If you could post the code you used that would be great.

thanks everyone.

 

[geraldcor]

Sorry it took me a while to reply, I got caught up doing things at work and forgot about this. Here is all of the code that I need to make things work as needed. I added all of the keydown code to handle using the arrow keys for navigation and still be able to use the mouse clicks.

I added lines to help make it more readable.

The second sub is really the only meaty part of the whole ordeal.

Private Sub PreContactSearch_Enter()
Me.ContactSearch.Visible = True
End Sub
'---------------------------------------------------------------------------------------------------------
Private Sub PreContactSearch_Change()
Dim SQLStatement As String
SQLStatement = "SELECT Customers.IDFLCustomerNum, tblContacts.ContactName FROM Customers INNER JOIN tblContacts ON Customers.IDFLCustomerNum=tblContacts.ClientID" _
    & " WHERE tblContacts.ContactName LIKE ""*" & Me.PreContactSearch.Text & "*"" ORDER BY tblContacts.ContactName;"
Me.ContactSearch.RowSource = SQLStatement
End Sub
'---------------------------------------------------------------------------------------------------------
Private Sub PreContactSearch_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyDown Or KeyCode = vbKeyUp Then
    Me.ContactSearch.SetFocus
End If
End Sub
'---------------------------------------------------------------------------------------------------------
Private Sub ContactSearch_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
If Button = vbKeyLButton Then
    Call ContactSearchUpdate
End If
End Sub
'---------------------------------------------------------------------------------------------------------
Private Sub ContactSearch_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyReturn Then
Call ContactSearchUpdate
End If
End Sub
'---------------------------------------------------------------------------------------------------------
Private Sub ContactSearchUpdate()
' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[IDFLCustomerNum] = " & str(Nz(Me![ContactSearch], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
'----------------------------------------------------------------------------------------------------------
Private Sub Detail_Click()
On Error GoTo Err_Detail_Click

If Me.ContactSearch.Visible = True Then
    Me.ContactSearch.Visible = False
Else
End If
Exit_Detail_Click:
    Exit Sub

Err_Detail_Click:
    Me.PreContactSearch.SetFocus
If Me.ContactSearch.Visible = True Then
    Me.ContactSearch.Visible = False
End If
    Resume Exit_Detail_Click
End Sub

 

[elliott_shafii]

Works really well.

Cheers

Elliott