Solved

Filter Subform with Text Box Control on Each Entry

Posted on 2012-04-05
12
488 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
ah-ha that's it.
Thx!
MV
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
mvasilevsky,

upload a copy of the db
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
Comment Utility
that is correct, BUT without this line

me.subformControlName.form.filteron=true

your first line is WORTHLESS
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
<<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
Comment Utility
whoa, i did not know that speaking of the obvious truth is an act of rudeness.
0
 
LVL 10

Author Comment

by:Michael Vasilevsky
Comment Utility
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
Comment Utility
Sounds like a plan.

:-)
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.

772 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

10 Experts available now in Live!

Get 1:1 Help Now