Sort/Fitler listbox or Search Listbox

[Behedwin]

Id like to either have a textbox where the user can type something and search in the data of an listbox.



Or it would work if I instead had a combobox where the user could select a theme and show the data connected to that theme in the listbox.

---

I have two tables
One called Profile_Table
One called Log_Table

I then have a form called Profile_Form that is based on a query Profile_Query (profile_query is a combination of the two tables)

In the form profile_form i have a listbox called NamePicker_Listbox
This listbox have 3 columns picking data from the profile_table


Now id like to add a textbox somewhere so the user using the form can type something and search in the columns of the listbox and filter out the rest of the data. For example if the user types "Jo", the listbox display all data containing "jo" somewhere in any of the columns of the listbox.


A second option if the search feature does not work
It would be great if i could filter the listbox... for example the profile_table contain the column "workplace location"
the user could select one of the workplace locations in a combobox and get all the records that have that workplace location.


In the best of worlds, i want both


But since im quite new to access, i have no clue how to start doing this.

I have uploaded my project here, so please have a look if you want to help!

[ranman256]

if you have a continous form showing all records,
user enters a value in the txtBox ,press enter, this will filter the results.
(in the afterupdate event)

sub txtBox_Afterupdate()
If IsNull(txtBox) Then
   Me.FilterOn = False
Else
   Me.Filter = "[ModelNo]='" & me.txtBox & "'"
   Me.FilterOn = False
End If
end sub

it can also work with combo box too.

[orange]

You may get some ideas from these search articles that are often referenced:

Allen Browne
Steve Bishop Filter
Steve Bishop Keyword
Good luck

[Behedwin]

if you have a continous form showing all records,
user enters a value in the txtBox ,press enter, this will filter the results.
(in the afterupdate event)

sub txtBox_Afterupdate()
If IsNull(txtBox) Then
   Me.FilterOn = False
Else
   Me.Filter = "[ModelNo]='" & me.txtBox & "'"
   Me.FilterOn = False
End If
end sub

it can also work with combo box too.

im not using continous form.
have a simple form with a listbox displaying all data records and then i select a data record in the listbox to "import" it to controls in my form.

[Behedwin]

Okey i have tried to work out a solution.

I have this code now connected to a commandbutton (on click event)

Then i have a textbox that act as a search field.

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

but when i type something in the textbox and clicks on the commandbutton.
When above code is running my listbox gets just empty.
Can you see any errors?

[Behedwin]

Still cant solve this.
Cant figure out why the LIKE statement wont work in the criteria area.
Ive tried many different ways but for some reason it wont work.
I just dont get any result or the result is that the listbox is empty.