Filtering list box row source

[okschlaps]

I have a list box which returns a pretty lengthy list of query results. I'd
like users to be able to narrow the list by entering the first few characters
in a text box. I've set up the text box with the following code

Private Sub Listfilter_Change()
On Error GoTo ErrHandler

Me.lstTRS.RowSource = "SELECT qryTRS.STR, qryTRS.[T-R-S]" _
& " FROM qryTRS WHERE (((qryTRS.[T-R-S]) Like " & Me.Listfilter.Value &
"*))" _
& " ORDER BY qryTRS.[T-R-S];"
Me.lstTRS.Requery

Exit Sub
....

It runs and clears the list but doesn't return results. I don't get an error
either, so I suspect I've just misused the syntax for the wild card. Or is
there an entirely different method I should be using.
Thanks for your help!

 

[Jeff L]

One thing I noticed is that you need single quotes around the string
for your like statement.

Like '" & Me.Listfilter & "*'))"

Hope that helps!

 

[okschlaps]

Jeff - that got the query to work! Thanks. But now I have another problem.
The new values don't actually show up in the list box until I exit the text
box, re-enter it and enter new values - then the filter from the previous
entry applies. (Like it's always one step behind. I've tried moving the
requery around to on exit, etc. but it does the same thing.

 

[okschlaps]

Figured it out. I needed to use the text property to get the current entry
instead of the saved.