Dynamic Search Box using 'Like'

[geraldcor]

Hello all,

The normal function of a combo box as a search method is great - as long as you search by the first letter of whatever it is you are looking for.

Some cases have come up where it would be nice to be able to search for June and get the result that is Xiang Xu (June). I can do this just fine with a 'Like' statement in SQL and display it in a msgbox, but the users want it to function like the combo box search where you type anything in, you see your result, you click on it and you are taken to the appropriate record. Is there any way of doing this sort of dynamic search in access?

Thanks for any and all help

Greg

 

[pbaldy]

It would help to see your code, but generally you'd need to add a wildcard before the search text as well as after.

 

[geraldcor]

An Example

Here is the code I use to populate a message box with the search results.

Private Sub AdvancedSearch_Click()
Dim strResults As String
Dim rst As Recordset
Dim SQLStatement As String
SQLStatement = "SELECT Customers.IDFLCustomerNum, tblContacts.ContactName, tblContacts.ContactNumber" _
                & "FROM Customers INNER JOIN tblContacts ON Customers.IDFLCustomerNum = tblContacts.ClientID" _
                & "WHERE Customers.IDFLCustomerNum = " & Forms!frmContacts!ClientID & " ORDER BY tblContacts.ContactName;"
                    
    Set rst = CurrentDb.OpenRecordset(SQLStatement)
    rst.MoveFirst

strResults = "" 'Initialize the variable

If IsNull(Me.AdvancedSearchText) Or Me.AdvancedSearchText = "" Then
    MsgBox "You must enter something in the box to the left in order to search", vbCritical, "Error"
Else
While Not rst.EOF
If rst!ContactName Like "*" & Me.AdvancedSearchText & "*" Then
    strResults = strResults & rst!ContactName & Chr(13)
End If

rst.MoveNext
Wend

If strResults = "" Then
    MsgBox "No contact names match your search criteria", vbCritical, "No Results Found"
Else
    MsgBox "The Following Contacts Match Your Criteria:" & Chr(13) & _
    strResults, vbInformation, "Search Results"
End If
End If
End Sub

The user types in "an" the msgbox says Jane, Anne, Pan etc.

 

[pbaldy]

Maybe I misunderstood the goal, so maybe you can clarify what you want to happen. For starters, that's an inefficient way to find the matches. Just add the Like statement to the recordset SQL, and the recordset will only return matching records. No need to loop through looking for matches.

 

[geraldcor]

Well, for this one, it needs to filter once by company ID, then from those records I want to find the matching contacts.

Edit: I guess it's true what you said about the inefficient search. I looked again and since I am not populating anything with that SQL, I can put the Like statement right in there. Good eye.

The goal is to have a combo box where I can type in "an" and instead of showing "andy" "andrew" "annihilate" etc it will show "andy" "andrew" "annihilate" "pan" "jane" etc. So far I can only get combo boxes to use the auto complete feature based on traditional letter ordering.

 

[pbaldy]

My point was that SQL like

...WHERE Customers.IDFLCustomerNum = " & Forms!frmContacts!ClientID & " AND ContactName Like '*" & Me.AdvancedSearchText & "*' ORDER BY...

Should return the matching records without the need for a loop through the entire recordset.

I doubt you'll get a combo box to act that way, but you could have the user type into a textbox and set a combo or list box to the matching records, enabling the user to choose from there. Would that work?

 

[pbaldy]

Oops, you must have edited after I started typing, so I didn't see that you realized what I meant. Sorry.

 

[geraldcor]

No Problem. It seems you may be right. I may have to do a two step search of some sort, which is done in other places so perhaps they will not murmur too much

Thanks.

 

[pbaldy]

I might have them typing in a textbox and dynamically change the rowsource of a listbox right underneath it so they could see the results as they type. When they see the record they want, they double click on it.

 

[geraldcor]

Oooooh! That's pretty good. When would be the best way to dynamically change the rowsource? i.e. onDirty, onEnter etc. How do I keep the listbox 'listening' to the TextBox?

 

[pbaldy]

You'd use the change event of the textbox, and set the rowsource of the listbox to SQL like you already use for the recordset (including the Like). The trick is, in the change event you have to use the .Text property of the textbox:

Me.AdvancedSearchText.Text

Depending on how you want it to work, I might test to make sure something is entered, or you'll get all records when the textbox is empty (which may be what you want).

 

[geraldcor]

Not sure I understand the .Text reference. On the OnChange event, I have AdvancedSearchText.Text doing what?

Oh. Wait. To reference the control itself I have to use the .Text. Silly me. Takes a little more time for things to set in.

 

[pbaldy]

You'd have this in the change event:

Dim SQLStatement As String
SQLStatement = "SELECT ..."
Me.ListBoxName.RowSource = SQLStatement

My point about the .Text was that in the portion of that SQL that refers to the textbox, you'd need to add the .Text to the form reference. The default when unspecified is the .Value property, which in the change event will not contain the current contents of the textbox.

 

[geraldcor]

Furthering the problem:

On change doesn't do anything until I exit the field. And, even after that, the listbox isn't updating based on that rowsource. I am doing a msgbox to tell me the rowsource after the text is changed and it seems ok. The listbox just won't update. Do I have to force it?

 

[geraldcor]

Got the onChange to work, but I can't get anything to populate in the list box. My SQL:

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;"

Is this wrong?

 

[pbaldy]

Try this:

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;"

I assume you're including the line to actually set the rowsource?

 

[geraldcor]

Alright. Enough is enough. I needed to add "" i.e. ""* & Me.PreContactSearch.Text & *""

It all works wonderfully now.

I did some cosmetics so that there is one text box and when the user types in that text box the list box becomes visible. Then oncurrent, the listbox disappears. Thanks pbaldy for helping me through this.

Greg

 

[geraldcor]

That would have worked, but some contacts have a ' in their name so I did the double quotes.

Thanks a million

Greg

 

[elliott_shafii]

I'm looking to do the same thing with the ability to search not just the against the first word.

Did you manage to do this?

 

[pbaldy]

Not just the first what? The method we landed on will search based on whatever is entered.