Solved

Filter Subform with Text Box Control on Each Entry

Posted on 2012-04-05
12
528 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37814167
mvasilevsky,

upload a copy of the db
0
 
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

732 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