Filter and Search a listbox poulated from a table

[behedwin]

I have a form called Profile_Form.
In this form i have a listbox called NamePicker_Listbox
It is populated with data from a table called Profile_Table.
The listbox have 3 columns, profile_ID, FirstName, LastName


I want to add a textbox (Text142) to my profile_form
When the user types in some data in the textbox (Text142) and press a commandbutton i want to display the result in the listbox. A search so to say.... If the user types in "o" in the textbox all records with an "o" should show in the listbox.

I have searched and watched videos and think i can solve this by adding this code to a commandbuttons on click event.

Dim sql As String

sql = "SELECT Profile_Table.Profile_ID, Profile_Table.FirstName, Profile_Table.LastName" _
    & "FROM Profile_Table" _
    & "WHERE Profile_Table.LastName LIKE '*" & Me.Text142 & "*' " _
    & "ORDER BY Profile_Table.LastName;"

Me.NamePicker_Listbox.RowSource = sql
Me.NamePicker_Listbox.Requery

Idea is that the WHERE statement is searching something LIKE the thinks typed into the texbox called Text142

But if i type something in the textbox and then press the button.
The listbox is just empty, no results.
Im thinking this is because im really only searching the first column and not all columns in the listbox.
Is this correct? If so how to fix it?

Or is there any other solution?

 

[alansidman]

Is this what you are trying to do?

http://www.datapigtechnologies.com/flashfiles/buildcustomfilter.html

 

[behedwin]

Is this what you are trying to do?

http://www.datapigtechnologies.com/flashfiles/buildcustomfilter.html

No, this not what i had in mind.
I have uploaded my file here, take a look and i think you will see what i mean.
Download: https://ufile.io/px391

I have a listbox displaying all records.
But i want to search in that listbox.

Open the profile_form form and you will see a large listbox called NamePicker. This is where all records from Profile_Table are displayed.
I want to search and filter in this listbox with the help of the textbox and button above it.

 

[xenou]

The sql in your first post is wrong because there are no spaces between the words. When you concatenate two lines you must include spaces, i.e.,

Not

sql = "Select * from Table" _
& "Where Value = 1"

rather

sql = "Select * from Table" _
& " Where Value = 1"

The first code gives: Select * from TableWhere Value = 1 (wrong)
The second code gives: Select * from Table Where Value = 1 (right)

You should always look at your SQL when you write raw SQL in code and make sure it is what you think it is.