Filter Subform with Text Box Control on Each Entry

What event/code should I use to produce the following functionality:
The user has a text box control and a subform. The subform is filtered based on one column for every entry the user makes in the text box. I.e. if the user enters "a" the subform is filtered by values which contain "a", then the user enters "ab" and the subform is further filtered, etc.
I'm sure someone has done this?

LVL 12
Michael VasilevskySolutions ArchitectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Use the on Change event of the textbox:

Me.SubformControlName.Form.Filter = "YourField LIKE '" & Me.txtBox.Text & "'"
Rey Obrero (Capricorn1)Commented:
use the change event of the text box

private sub text0_change()

sFilter="[columnName] like '*" & text0.text & "*'"

me.subformControlName.form.filter= sfilter

end sub
Michael VasilevskySolutions ArchitectAuthor Commented:
I tried OnChange, but find if I enter a blank textbox and start typing, the value of the text box remains null until I tab out of it.
E.g. enter textbox, value is null
type "a", value is null
type "b", value is null
tab out or shift+enter to update control, value is now "ab"

so I need to update the control on each change, but I don't see that.
Any ideas?
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Make sure you are using the TEXT property.

I forgot to include the asterisk in my post:

Me.SubformControlName.Form.Filter = "YourField LIKE '" & Me.txtBox.Text & "*'"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Michael VasilevskySolutions ArchitectAuthor Commented:
ah-ha that's it.
Rey Obrero (Capricorn1)Commented:

upload a copy of the db
The Change Event fires with every change that is made to the text in the textbox.

The Text property the text in the box as it is seen real-time.

as opposed to this:
Me.SubformControlName.Form.Filter = "YourField LIKE '" & Me.txtBox & "*'"

Which defaults to the value (or saved) data -- which only changes as you noticed when you tab out of or otherwise leave the field.
Rey Obrero (Capricorn1)Commented:
that is correct, BUT without this line


your first line is WORTHLESS
<<your first line is WORTHLESS>>


That was needlessly rude.  

If you have an issue with a closure, please use the Request Attention button instead of posting insulting comments.  Depending on the individual posting a question, a detail like that may be understood (or not).

My understanding here was that the actual Event and the Text property were the missing links.
Rey Obrero (Capricorn1)Commented:
whoa, i did not know that speaking of the obvious truth is an act of rudeness.
Michael VasilevskySolutions ArchitectAuthor Commented:
I agree that saying any part of anyone's post is "worthless" could be construed as rude.
Indeed, the piece I was stuck on was the .text property and I needed that explicitly stated apparently.

Capricorn1, I appreciate your input on this question and look forward to supporting both of you in your continued domination of the EE MS Access topic :-)

Let's get back to solving the world's problems!
Sounds like a plan.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.