Solved

Form vb for Unbound Listbox filtering based on Unbound Textbox

Posted on 2014-01-01
2
925 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

739 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