Solved

Filter Subform with Text Box Control on Each Entry

Posted on 2012-04-05
12
498 Views
Last Modified: 2012-04-05
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?
Thx,

MV
0
Comment
Question by:Michael Vasilevsky
  • 5
  • 4
  • 3
12 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 37814061
Use the on Change event of the textbox:


Me.SubformControlName.Form.Filter = "YourField LIKE '" & Me.txtBox.Text & "'"
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37814071
use the change event of the text box

private sub text0_change()

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

me.subformControlName.form.filter= sfilter
me.subformControlName.form.filteron=true

end sub
0
 
LVL 10

Author Comment

by:Michael Vasilevsky
ID: 37814146
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?
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 37814157
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 & "*'"
0
 
LVL 10

Author Comment

by:Michael Vasilevsky
ID: 37814163
ah-ha that's it.
Thx!
MV
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37814167
mvasilevsky,

upload a copy of the db
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 61

Expert Comment

by:mbizup
ID: 37814170
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.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37814181
that is correct, BUT without this line

me.subformControlName.form.filteron=true

your first line is WORTHLESS
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37814199
<<your first line is WORTHLESS>>

Rey,

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.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37814325
whoa, i did not know that speaking of the obvious truth is an act of rudeness.
0
 
LVL 10

Author Comment

by:Michael Vasilevsky
ID: 37814343
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!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37814349
Sounds like a plan.

:-)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now