Improve company productivity with a Business Account.Sign Up

x
?
Solved

Form vb for Unbound Listbox filtering based on Unbound Textbox

Posted on 2014-01-01
2
Medium Priority
?
1,013 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 86

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

607 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