Solved

Form vb for Unbound Listbox filtering based on Unbound Textbox

Posted on 2014-01-01
2
866 Views
Last Modified: 2014-01-01
Happy New Year to All!

Trying to filter an unbound listbox of last names based upon the users input in an unbound textbox as they input each character in that textbox.  However I'm missing something.

The RecLName field is set as Text

Private Sub txtLName_Change()
Dim strLName As String, lngLenLName As Long, lngSortSel As Long
lngSortSel = Me.frameExistingParties.Value

    If IsNull(Me.txtLname.Text) = False Then
        strLName = Me.txtLname.Text
        lngLenLName = Len(Me.txtLname.Text)
        If lngSortSel = 1 Then
            Me.lstExistingParties.RowSource = "SELECT RecPartyID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName FROM qrytbl_Parties WHERE RecLName LIKE " & "'" & strLName & "' ORDER BY SortName ASC "
        Else
            Me.lstExistingParties.RowSource = "SELECT RecPartyID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName FROM qrytbl_Parties WHERE RecLName LIKE " & "'" & strLName & "' ORDER BY SortName ASC "
        End If
    Else
    End If

End Sub

Open in new window


My listbox simply goes blank after I input the first character in that textbox
0
Comment
Question by:wlwebb
2 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
You're using LIKE with a finite value - in other words, if I typed an 'a', your code is asking for all records where RecLName is LIKE 'a'. Unless you have a record where the value in RecLName is 'a' (and ONLY 'a') then you wouldn't find anything.

You need to use the wildcard operator with LIKE:

 Me.lstExistingParties.RowSource = "SELECT RecPartyID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName FROM qrytbl_Parties WHERE RecLName LIKE '"  strLName & "*' ORDER BY SortName ASC "

Open in new window


Note the asterisk ( * ) AFTER the strLName variable. So if I typed 'art' it would find arthur, atrid, etc.  It would not find McArthur, however. to do that, you could also use:

 Me.lstExistingParties.RowSource = "SELECT RecPartyID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName FROM qrytbl_Parties WHERE RecLName LIKE '*"  strLName & "*' ORDER BY SortName ASC "

Open in new window


Note there are preceding and trailing asterisks. This would find any instance of the value typed. For example, if I typed 'art' it would find arthur, artid, etc,
0
 

Author Closing Comment

by:wlwebb
Comment Utility
Thank you Scott!  Missed that one.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now