Solved

Filter Subform with Text Box Control on Each Entry

Posted on 2012-04-05
12
524 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 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

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…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

679 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