Solved

Form vb for Unbound Listbox filtering based on Unbound Textbox

Posted on 2014-01-01
2
918 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
ID: 39749640
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
ID: 39749642
Thank you Scott!  Missed that one.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

685 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