Search as you type - Combo box

[Lateral]

Hi guys

Firstly, let me say that I have spent the last 3 hours searching this forum and Googling regarding "Search as you type" Combo Box functionality and it's giving me a headache!

I am using Access 2007 and have a form that has a Combo Box that links to a Parts table (it has 10 fields) that contains a list of car parts. The application is similar to an invoicing system and allows the user to "build/create" an invoice containing a list of car parts.

The user can use a Combo Box to select which car part that want to attach to the invoice and the invoice can have many car parts attached to it.

I have managed to implement a very simple "Search as you type" function using the standard Access 2007 functionality without any Event Procedures etc.

The problem is that the "Search as you type" only searches from the start of the field that is being searched. For example, if the user types in "OIL" they drop down list only displays records that start with "OIL" and not records that contain the text "OIL". As an example, it only finds:

OIL-FILTER

but doesn't find

FILTER-OIL

I really need it to find "FILTER-OIL"

How can I do this easily? I'm sure I'm missing something simple but I just can't seem to understand how to do this from the many examples I have found.

Thanks for any help you can provide me.

Best Regards
Greg

 

[jdraw]

Search as you type typically works left to right. It keeps refining the results based on the characters entered. So that search won't find FILTERS_OIL if you enter OIL.

 

[Lateral]

Thanks for the reply. I'm already aware of what the limitations are. I need a solution to the problem I outlined.

There must be a way to do this.

 

[jdraw]

..where YourField Like "*" & "OIL" & "*"

will find Oil anywhere in the Field , but this is not Search as you type.

 

[Lateral]

Thanks again for the reply.

Can you please be more specific (I'm a newbie )

 

[jdraw]

I recommend you do some searches in the forum for "search form".

Here's a video search tutorial that should help. It's 2003 based but the concepts and coding are the same.

 

[MarkK]

I would do this with a TextBox and a ListBox. Handle the Change event of the TextBox and rewrite the SQL in the RecordSource of the ListBox for each keystroke, specifically, rewrite the WHERE clause of the SQL to search every field you want to search using . . .

"WHERE Field1 LIKE '*[I]<your value>[/I]*' OR Field2 LIKE '*[I]<your value>[/I]*'

. . . noting that there is a * wild-card character at the start and end of each comparison value, so it will find any substring.

Hope this helps,

 

[Lateral]

Thanks guys

How do I take what is typed by the user in the Combo Box and insert it into a "like" statement that I have added to the SQL Query that is attached to the Combo Box?

I have added the following Like statement to the PartName field and it manually run the query and it works perfectly as I want it:

Like "*USA*"

This Like statement finds all records that contain the text "USA" in any part of the field PartName.

Regards
Greg

 

[MarkK]

Here's a sample to look at . . .

Function GetWhereClause() As String
   dim vFields as variant
   dim vField as variant
   dim where as string
   [COLOR="Green"]'create an array of field names[/COLOR]
   vFields = Split("Field1 YourField SampleField DateField")
   [COLOR="Green"]'enumerate fields, creating where clause[/COLOR]
   for each vField in vFields
      where = where & "OR " & vField & " LIKE '*" & Me.tbSearch.Text & "*' "
   next
   [COLOR="Green"]'add the word "WHERE " and drop the leading "OR "[/COLOR]
   GetWhereClause = "WHERE " & Mid(where, 4)
End Function

See what's happening there?

 

[CJ_London]

I'm not having a good day but isn't Where a reserved word?

 

[smig]

I made exactly what you are looking for, including the ability to put spaces to search for seversl words.
I'll try to put a sample in the code repository part of the forum.
Will let you know when it will be done.

Basicely it is based on what MarkK said

 

[Lateral]

Hi guys,

Smig, you're a legend!

I look forward to hearing from you soon..

Thanks again guys for all of your help....I can feel that we are nearly there!

Regards
Greg

 

[smig]

I just uploaded the "Search As you Type" to the Code Repository.
It should be visible in a day or two.

 

[Lateral]

Hi Smig

Thanks for doing that for me but I'm having problems understanding how it all works and how to retrofit it to my existing code.

I have the following query attached to a Combox Box called "PartID":

SELECT DISTINCTROW Parts.*, Parts.Web_Category, Parts.PartDescription, Parts.UnitPrice, Parts.KitID, Parts.DefaultQty, Parts.Notes, Parts.PartName
FROM Parts
WHERE (((Parts.PartName) Like '*cam*') AND ((Parts.Inactive)=0))
ORDER BY Parts.PartName;

Note that I have added the "Like '*cam*'" condition so that only parts that contain the text "cam" in them will be displayed in the combo box list. This works perfectly.

I have a question, can I somehow "parameterize" the "Like '*cam*'" condition part of the query so that it "takes" the text that I want to use to filter the records from the combo box that it is connected to???

Thanks again for your help and I'm sorry that I'm struggling with this...

Regards
Greg

1.

 

[smig]

I just uploaded a new version using Field1 and Field2 as the names. I think it will be more easy to understand.
Probably be there in a day or two

what you really need to look into is the UpdateSearchAsYouTypeList sub in the mdl_Fuchs_SearchPopUps modue
This is where the search job is done.

Public Sub UpdateSearchAsYouTypeList(frm As Form)

On Error GoTo errHere


Dim rs As DAO.Recordset

Dim QRY_Search As String
Dim QRY_WHERE_CLUES As String

Dim Count_ReshimatSearchAsYouTypeNameim As Integer
Dim strSearchAsYouTypeNameSplit() As String
Dim strSearchAsYouTypeSerialSplit() As String
Dim i As Integer

Set db = CurrentDb()

[COLOR="DarkRed"]' ---- Change this to be your primaryKey[/COLOR]
QRY_WHERE_CLUES = " WHERE [[COLOR="DarkRed"]SearchAsYouTypeID[/COLOR]] Is Not Null "

[COLOR="DarkRed"]' ---- Change these to be your Search in fields (You have 2)[/COLOR]
If NullOrEmpty(pbSearchAsYouTypeField1) = False Then
    strSearchAsYouTypeField1Split = Split(pbSearchAsYouTypeField1, " ")
    For i = LBound(strSearchAsYouTypeField1Split()) To UBound(strSearchAsYouTypeField1Split())
        QRY_WHERE_CLUES = QRY_WHERE_CLUES & " AND [[COLOR="DarkRed"]SearchAsYouTypeField1[/COLOR]] Like " & Chr(34) & Chr(42) & strSearchAsYouTypeField1Split(i) & Chr(42) & Chr(34) & ""
    Next i
End If

If NullOrEmpty(pbSearchAsYouTypeField2) = False Then
    strSearchAsYouTypeField2Split = Split(pbSearchAsYouTypeField2, " ")
    For i = LBound(strSearchAsYouTypeField2Split()) To UBound(strSearchAsYouTypeField2Split())
        QRY_WHERE_CLUES = QRY_WHERE_CLUES & " AND [[COLOR="DarkRed"]SearchAsYouTypeField2[/COLOR]] Like " & Chr(34) & Chr(42) & strSearchAsYouTypeField2Split(i) & Chr(42) & Chr(34) & ""
    Next i
End If

[COLOR="DarkRed"]' --- Change these to be your PrimaryKey andSearch in fields (You have 2) and table[/COLOR]
QRY_Search = "SELECT DISTINCT [[COLOR="DarkRed"]SearchAsYouTypeID[/COLOR]], [[COLOR="DarkRed"]SearchAsYouTypeField1[/COLOR]], [[COLOR="DarkRed"]SearchAsYouTypeField2[/COLOR]]  " & _
    " FROM [[COLOR="DarkRed"]tblSearchAsYouType[/COLOR]] " & _
    QRY_WHERE_CLUES & _
    "ORDER BY [[COLOR="DarkRed"]SearchAsYouTypeField1[/COLOR]] "

frm.SearchAsYouTypeList.RowSource = QRY_Search

frm.SearchAsYouTypeList.Requery


ExitHere:
    Exit Sub

errHere:
    MsgBox "mdl_SearchPopUps" & "," & "UpdateSearchAsYouTypeList" & "Error:" & Err & "-" & Err.Description
    Resume ExitHere

End Sub

Don't change anything in the form nor any other piece of code. This will require you for you a lot of changes.

 

[Lateral]

Hi Smig

Firstly, thanks for taking the time to do this.

I'm confused as I don't understand what you are telling me to do when you say "Don't change anything in the form nor any other piece of code".......and "what you really need to look into is the UpdateSearchAsYouTypeList sub"

Regards
Greg

 

[smig]

in the module mdl_Fuchs_SearchPopUps modue you can find the UpdateSearchAsYouTypeList sub.
This is the only piece of code you need to make changes in (You will need to put your table)
I marked in red the exact pieces of code need to be changed.

After you make sure everything works you can start to learn how it works and make more changes if you want.
I have a similar form with 5 fields to search in.

The SetSearchPopUps sub will set the events for all controls on form.
the SearchAsYouTypeList function is the function for the SearchAsYouTypeField1 and SearchAsYouTypeField1 controls' .OnGotFocus event
The SearchAsYouTypeField1Change function is the function for the SearchAsYouTypeField1Search contol's .OnChange event.
The SearchAsYouTypeField2Change function is the function for the SearchAsYouTypeField2Search contol's .OnChange event.
The ClosePopUpsAll function is the .OnGotFocus event for all other controls on form. This function will close all search controls.