Wildcard search within ComboBox to control RowSource

[CaptainKen]

I have a combobox in the forms header. Column 1 is the bound column and LimitToList is set to Yes.

I'd like the users to be able to type in a text string that would be treated like a wildcard to populating the cbo with the values in the 2nd column are LIKE the value that was typed in. So typing in "riv" would return records such as At The Riverside, or Riverview Resort, or The Riverside Resort.

Private Sub cboSelectCompany_Change()

    Dim strSQL As String

    strSQL = "SELECT tblComp.[CompID], tblComp.[CompName] FROM [tblClients/Prospects] tblComp " & _
                "WHERE [CompName] Like '*" & [Forms]![frmCompanyContacts]![cboSelectCompany].Column(1) & "*' " & _
                "ORDER BY [CompName];"

    Me.cboSelectCompany.RowSource = strSQL
    Me.cboSelectCompany.Dropdown
    Debug.Print "Me.cboSelectCompany.RowSource: " & strSQL

End Sub

Debug.Print is returning

When I type "r" I get:
Me.cboSelectCompany.RowSource: SELECT tblComp.[CompID], tblComp.[CompName] FROM [tblClients/Prospects] tblComp WHERE [CompName] Like '*R & R Products, Inc.*' ORDER BY [CompName];

When I type the next letter "i" I get:
Me.cboSelectCompany.RowSource: SELECT tblComp.[CompID], tblComp.[CompName] FROM [tblClients/Prospects] tblComp WHERE [CompName] Like '**' ORDER BY [CompName];

When I type the 3rd letter "v" I get:


Me.cboSelectCompany.RowSource: SELECT tblComp.[CompID], tblComp.[CompName] FROM [tblClients/Prospects] tblComp WHERE [CompName] Like '*Riverview Resort*' ORDER BY [CompName];

Thank you,


Ken

[CaptainKen]

I have also tried the populate the above cbo using an unbound txtSearchForm to type the wildcard search criteria. Using the code below no value being typed in is returned. So a blank MsgBox opens each time I type in a character.

Private Sub txtSearchForm_Change()

    Dim strSQL_Comp As String

    MsgBox Me.txtSearchForm

    Me.txtSearchForm.SetFocus
    Me.txtSearchForm.SelStart = Len(Me.txtSearchForm.Value) + 1

    strSQL_Comp = "SELECT tblComp.[CompID], tblComp.[CompName] FROM [tblClients/Prospects] tblComp " & _
                            "WHERE [CompName] LIKE '*" & Me.txtSearchForm & "*' " & _
                            "ORDER BY CompName"
    
    strSQL_Contact = "SELECT [qryCompanyContacts].[ContactID], [qryCompanyContacts].[ContactName], [qryCompanyContacts].[Title], [qryCompanyContacts].[Div/Dept] " & _
                        "FROM [qryCompanyContacts] " & _
                        strWhere = ""

            Me.cboSelectCompany.RowSource = strSQL_Comp
            Me.txtSearchForm.SetFocus
            Me.txtSearchForm.SelStart = Len(Me.txtSearchForm.Value) + 1
            'Me.cboSelectCompany.Requery
            Debug.Print strSQL_Comp

End Sub

This is what the SQL looks like no matter what I type into txtSearchForm, this statement is returned each time I type a character.
SELECT tblComp.[CompID], tblComp.[CompName] FROM [tblClients/Prospects] tblComp WHERE [CompName] LIKE '**' ORDER BY CompName

[SteveF]

Try using the "After Update" event instead of "change"; also make sure that the "Auto Expand" property of the combo is set to "true".

Change fires on every keystroke; After Update only fires when you're done typing and leave the control.

[CaptainKen]

Thanks for your reply Steve.

Your suggestion works if I use the AfterUpdate of the txtSearchForm. How can I modify it to make it work OnChange of the txtSearchForm? It's important to see the results as they type.

[SteveF]

Ken,

Based on the thread, it appears that you are trying to use value of a text box to filter the list of values in a combo.

You don't need to do that -- properly configured, a combo will handle this itself without any need for external input. That "AutoExpand" property I mentioned in my previous post is the key to that behavior.

In addition, use some VBA code in the combo's GotFocus event to drop-down the combo automatically:

Private Sub MyComboName_GotFocus()
'automatically open the combo

    MyComboName.Dropdown
    
End Sub

With those two items added to your project (AutoExpand and VBA), typing DIRECTLY IN THE COMBO will automagically jump down the list of entries in the combo to the closest match to what's been typed. No need to have an external control to achieve the same thing.

Steve

[CaptainKen]

Thanks Steve, but I'm familar with the default behavior of combo boxes allowing you to simply start typing to find a match the *begins* with the string.

In my original post I said:

I'd like the users to be able to type in a text string that would be treated like a wildcard to populating the cbo with the values in the 2nd column are LIKE the value that was typed in. So typing in "riv" would return records such as At The Riverside, or Riverview Resort, or The Riverside Resort.

So I need a wild card where whatever is typed is found anywhere in the combobox.

[SteveF]

OK, I missed that part. Usually typing in the start of something is what people want.

I don't see anything seriously wrong with your SQL, however Access' SQL syntax is definitely not fully ANSI-compliant. One thing you should try just to make sure it's not a problem is to be more explicit with your table alias:

...FROM [tblClients/Prospects] AS tblComp...

'cause I'm not sure that Access will recognize the alias without the AS.

Also, as an experiment, drop a text box on your form, and populate that with the combo contents when the Change event fires. See what you get in the text box. If that's OK, try rewriting your code to use the contents of the text box for the SQL statement, and hide the text box on the form.

Steve

[CaptainKen]

Do you mean the Change event for
txtSearchForm_Change or cboSelectCompany?

Isn't an unbound textbox pretty much the same as using Debug.print?

[SteveF]

Your original message was using the Change event for cboSelectCompany. This is the Change event I was referring to.

Debug.Print is fine during the development process, but it really won't store strings or variable contents at run-time. That's why the hidden text box. There are also other things going on -- events firing, etc. -- that can interfere with code working as you think it should.

For test purposes, I'm just proposing something like:

Private Sub cboSelectCompany_Change()


    txtHidden = cboSelectCompany.Column(1)


End Sub

If txtHidden echoes what's being typed into (or selected) in the combo, then you can add the rest of the code:

Private Sub cboSelectCompany_Change()


    Dim strSQL As String


    txtHidden = cboSelectCompany.Column(1)


    strSQL = "SELECT tblComp.[CompID], tblComp.[CompName] FROM [tblClients/Prospects] tblComp " & _
                "WHERE [CompName] Like '*" & txtHidden & "*' " & _
                "ORDER BY [CompName];"


    With cboSelectCompany
        .RowSource = strSQL
        .Requery
        .Dropdown
    End With





End Sub

Personally speaking, however, using the Change event of a control to modify the RowSource of that same control is not something that I would do or recommend. Instead, I'd probably drop an unbound text box on top of the combo, and have the user type in the search text there; using the Change event of the text box to filter the combo's RowSource. Use the Tab Order of the controls so that the user tabs out of the text box into the combo to make the final selection.

Steve

[CaptainKen]

The txtHidden is populating, but the search is still starting at the beginning of the company name, not as a wild card to search anywhere within the name.

Also when I attempt to use the Requery I get the error: "You must save the current field before you run the Requery action."

BTW, the Bound Column is set to 1 and the LimitToList is set to Yes. It won't let me change the LimitToList to No. Could this be an issue?

[CaptainKen]

I just tried another simple test using 2 fields.

Private Sub txtTest_Change()

Me.txtHidden = Me.txtTest

End Sub

As I type nothing is displayed in txtHidden. However, when I clear what I typed into txtTest, txtHidden then shows what I cleared. Kinda acting more like AfterUpdate.

[SteveF]

What's the default RowSource for cboSelectCompany?

You might need to add an explicit save into the code for the combo Change event:

Private Sub cboSelectCompany_Change()




    Dim strSQL As String




    txtHidden = cboSelectCompany.Column(1)




    strSQL = "SELECT tblComp.[CompID], tblComp.[CompName] FROM [tblClients/Prospects] tblComp " & _
                "WHERE [CompName] Like '*" & txtHidden & "*' " & _
                "ORDER BY [CompName];"


'save any unsaved records
    Me.Dirty = False


    With cboSelectCompany
        .RowSource = strSQL
        .Requery
        .Dropdown
    End With


End Sub

For the 2-text-box test, try adding 1 little line into the code:

Private Sub txtTest_Change()

    Me.txtHidden = Me.txtTest
    DoEvents

 End Sub

DoEvents is the equivalent of a "refresh" button in the code; it forces any pending processes (like updating the contents of a text box) to finish before going to the next step.

All of this is strictly theoretical at this point; I'm throwing in things that have successfully worked for me in similar situations. If you don't make any further progress, and your application does not contain critical and confidential data, I'm willing to take a look at it and see if I can figure it out.

Steve

[CaptainKen]

Getting this error "The text you entered isn't an item in the list" on the Me.Dirty.

I think I can extract only the parts I need into a sample mdb and upload.

[CaptainKen]

I've attached a stripped down sample of the database.

[SteveF]

OK, I downloaded the file. After examining the form, I need to confirm the expected workflow in the header section:

The user chooses a company; the list of available companies is filtered by the results of the wildcard search.
After a company is chosen, the list of contacts for that company is made available for selection.

Correct?

Steve