Filtering a Listbox Using a Textbox - Code Problem?

[mark.s]

I'm still learning access, but I'm just trying to get a basic search/filter going where the text entered into a textbox will filter the values in a listbox. The code looks like this:

Private Sub TextBox_Change()

Dim strSQL As String


strSQL = "SELECT ItemCode FROM TestTable WHERE ItemCode Like "
strSQL = strSQL & "'" & Me.TextBox.Text & "*'"
strSQL = strSQL & " ORDER BY ItemCode;"
Me.ListBox.RowSource = strSQL


End Sub

I'm not getting any errors or anything, but it's just not working, I can type into the textbox and nothing happens. I've attached the Access file too, in case that helps.

[ItsMe]

I use the line return in different areas than your example. I keep staring at it and I can't seem to get it to work.
I can offer you this example working example of mine.

Me.cmbPO.RowSource = "SELECT [qryShipmentCount].[CustID], [qryShipmentCount].[PO], [qryShipmentCount].[Unit_Count] " _
                     & "FROM [qryShipmentCount] " _
                     & "WHERE [PO] LIKE '" & Me.txtRowsource & "*'" _
                     & "ORDER BY [PO];"

Your fields would probably look like

Me.ListBox.RowSource = "SELECT ItemCode " _
                                  & "FROM TestTable " _
                                  & "WHERE [ItemCode] LIKE '" & Me.TextBox.Text & "*'" _
                                  & "ORDER BY [ItemCode];"

[mark.s]

I tried changing it to your format, I notice now that I'm getting a black highlight in the listbox when values should be showing up and it disappears when there should be nothing there.

Other than that though, it's still not working, I have no idea why not, the code looks completely fine to me, I've seen many examples on the net and the code is almost identical to that. I thought maybe it could be a problem with the data types of the listbox or the textbox, but I think they're fine.

So I have no real idea why it's not working still..... Thanks though.

[ItsMe]

I may have a chance to look at your file later. Is it a something that you can format as Access 2003? That would be of help to me and I may be able to get to it sooner.

[Dal Jeanis]

There are two columns in your listbox, and your Rowsource only contains one. Add the ID field to the left of the Itemcode.

strSQL = "SELECT ID, ItemCode FROM TestTable " & _
         "WHERE ItemCode Like '" & Me.TextBox.Text & "*' " & _
         "ORDER BY ItemCode;"
Me.ListBox.RowSource = strSQL

[mark.s]

Mmm tried that, didn't work either. I should be able to leave the ID out though shouldn't I? I didn't really want it to display.

I was looking again at that highlight that shows up in the listbox, I can copy and paste the highlight into notepad and it displays the correct value, so it looks like the values are being put in the listbox fine, they just aren't visible for some reason. I thought it might be a formatting problem, I've tried change things, but I can't see what it may be.

I've attached a 2003 version of the Access file, you can see what I mean if you type an "I" into the textbox and copy the highlights that show up in the listbox.

[ItsMe]

Here you go.

Test.zip

All of the properties were good in your listbox. All I did was add the second column to the SELECT statement.

The properties were 2 columns so the list box new what to do with both the ID column and the Text column. Always a good idea to build your listboxes and combos this way. With an ID column, the way you had it.

[ItsMe]

You are probably burned out on list boxes and SQL. but there is a sample DB I uploaded to the forum you might use as a reference.

It illustrates cascading combo boxes. I used it to help me with this problem here.

https://www.accessforums.net/sample-...xes-38171.html

[June7]

This works:

1. listbox RowSource
SELECT [TestTable].[ID], [TestTable].[ItemCode] FROM TestTable WHERE ItemCode LIKE "*" & [TextBox] & "*" ORDER BY [ItemCode];

2. VBA code
Private Sub TextBox_AfterUpdate()
Me.ListBox.Requery
End Sub

If you want the listbox to update after each character is typed, that is a bit trickier. Here is example that filters a form records. Might be adaptable for a listbox. http://allenbrowne.com/AppFindAsUType.html

[mark.s]

Here you go.

Test.zip

All of the properties were good in your listbox. All I did was add the second column to the SELECT statement.

The properties were 2 columns so the list box new what to do with both the ID column and the Text column. Always a good idea to build your listboxes and combos this way. With an ID column, the way you had it.

I am not sure what's going on here, but I'm having the exact same issue, it's just showing up as a highlight which I can copy out, but the actual value is not showing up in the listbox.

This works:

1. listbox RowSource
SELECT [TestTable].[ID], [TestTable].[ItemCode] FROM TestTable WHERE ItemCode LIKE "*" & [TextBox] & "*" ORDER BY [ItemCode];

2. VBA code
Private Sub TextBox_AfterUpdate()
Me.ListBox.Requery
End Sub

I tried this too, also does the same thing. I type a value then push enter, I can highlight the values in the listbox and copy and paste them out, but it doesn't actually display a value. It shows up like this:




Is there something wrong with my Access maybe?

[ItsMe]

It worked for me. Maybe the font needs to be adjusted. Like I mentioned, I first looked at your property settings. You have the ID hidden and the Text showing.

I will work up another from scratch.

[ItsMe]

I imported the table but built the rest from scratch.
SampleListBox.zip

[mark.s]

Oh cool, that works. But I don't understand about why the old one wasn't working or that part about the ID being hidden and text showing. Is that a property of the ListBox that needed to be changed?

I've tried making my own from that table again and it's showing the IDs in the table, but not the actual ItemCode, I've used your code so it must be a setting?

Sorry if these are stupid questions, trying to figure it out.

[ItsMe]

You can adjust the Column Widths in the "Format" tab of the ListBox properties. If you don't want to show a column sucha as an ID you can hide it by using the value 0 for its width. If you have two columns in the rowsource and you want only the second column to show you would paste this.

0";1.5"

I think you can change the units of measurement too. This example is in inches "

[mark.s]

Ok that worked, I had to actually change the column count too. Thanks a lot for all your help, finally all sorted.