Filter a Listbox with text from Textbox

ijit · Sep 1, 2017

I have a form with an unbound Listbox that finds a record on a form. When you select the Company Name, the two subforms show the records associated with the selected company name. The listbox contains thousands of companies. I would like to be able to enter text into a textbox that filters the Listbox wth any matches. I though about changing the Record Source of the List box to:

SELECT tblNavigatorLeads.DotNo, tblNavigatorLeads.LegalName
FROM tblNavigatorLeads
WHERE (((tblNavigatorLeads.LegalName) Like "*[forms]![frmContactHistory]![Text26]*"))
ORDER BY tblNavigatorLeads.LegalName;

It just makes the listbox go blank when I enter something into the text box. Any thoughts?

static · Sep 1, 2017

Like "*" & [forms]![frmContactHistory]![Text26] & "*"

ijit · Sep 15, 2017

Alright, I tried this code and it says, Run-Time 13 Type mismatch error.

Code:

Private Sub txtSearch_AfterUpdate()
    Debug.Print
    Dim strSQL As String
    Dim vText As String
    
    vText = Me!txtSearch

    strSQL = "SELECT tblNavigatorLeads.DotNo, tblNavigatorLeads.LegalName FROM tblNavigatorLeads WHERE (((tblNavigatorLeads.LegalName) Like " * " & [Forms]![frmContactHistory]![Text26] & " * ")) ORDER BY tblNavigatorLeads.LegalName;"


    Me.List19.RowSource = strSQL
    Me.List19.Requery
End Sub

MarkK · Sep 15, 2017

Presumably this txtSearch textbox, and the listbox List19 of search results are on the same form, and if so you should be able to do...

Code:

Const LIST_SQL As String = _
    "SELECT DotNo, LegalName " & _
    "FROM tblNavigatorLeads " & _
    "WHERE LegalName LIKE p0 " & _
    "ORDER BY LegalName;"
    
Private m_qdf As DAO.QueryDef

Property Get ListQuery() As DAO.QueryDef
    If m_qdf Is Nothing Then Set m_qdf = CurrentDb.CreateQueryDef("", LIST_SQL)
    Set ListQuery = m_qdf
End Property

Private Sub form_open(Cancel As Integer)
    FilterList
End Sub

Private Sub txtSearch_Change()
    FilterList Me.txtSearch.Text
End Sub

Private Sub FilterList(Optional criteria As String)
    criteria = "*" & criteria & "*"
    With Me.ListQuery
        .Parameters(0) = criteria
        Set Me.list19.Recordset = .OpenRecordset
    End With
End Sub

See how all those chunks work together to make a very robust and responsive text search capability? This uses a parameterized querydef to open a new recordset for every keystroke you type into the txtSearch textbox (and it just shows everything on form_open).
hth
Mark

BigHappyDaddy · Sep 15, 2017

try this:

Code:

Like '*" & [Forms]![frmContactHistory]![Text26] & "*'

Filter a Listbox with text from Textbox

ijit

Registered User.

static

Registered User.

ijit

Registered User.

MarkK

bit cruncher

BigHappyDaddy

Coding Monkey Wanna-Be

Similar threads

Users who are viewing this thread