We help IT Professionals succeed at work.

Access Code - Filter grid based on contents of search field

Helllo, I have what I think is a simple question.

I have a "customer grid" form (simple summary continuous form that shows a record for every customer in our databse).  The list of customers has gotten very long, so I want to put a single filter field int he header of the form.  

* Lets call this search/filter field "FilterInput", and say it is unbound.
* Lets say there's a field for each customer called ContactShortName (short name of the client)

I just want an AfterUpdate event that does a LIKE or CONTAINS filter based on what is typed into the search/filter box.  For example, if a user types "Micro" into FilterInput, it would filter the list of clients in the form to just clients who's ContactShortName CONTAINS the text "Micro".  So "Microsoft Inc" would show, as would "Advanced Micron", etc.

Has to be easy right?  I've used variations of the code I inserted below to no avail--get error messages including type mismatch, etc.  Can someone help?

Follow-up question: After properly setting the filter, do I do a DoCmd.ApplyFilter to apply it?  Or FilterOn = True?  Thanks.
Me.Filter = ("frmCustomersGrid") And (Contacts.ContactShortName) Like "*" & Me.FilterInput & "*"
Me.FilterOn = True

Open in new window

Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
You need to embed text fields in quotes:

Me.Filter = ("frmCustomersGrid") And (Contacts.ContactShortName) Like " & Chr(34) & "*" & Me.FilterInput & "*" & chr(34)
Me.FilterOn = True
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
Not sure why you have the form name in there, though.

Try this;
\
Me.Filter = "Contacts.ContactShortName Like " & Chr(34) & "*" & Me.FilterInput & "*" & chr(34)
Thank you for the quick resonse.  Still getting a type mismatch on the first line.

Contacts is the name of the table.  Anything else I can check/tweak?
 
That works!  Thank you very much.
Then could I also have a button to remove the filter that just simply did: Me.FilterOn = False?
I've requested that this question be closed as follows:

Accepted answer: 0 points for UptimeSystems's comment http:/Q_27431827.html#37084124

for the following reason:

ty
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Yes...

Generally for a 'filter off' button, you would 1) clear the filter and 2) set filter on to false:

Me.Filter = ""
Me.FilterOn = false
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
<That works!>

Did you know that you are marking your own comment as the accepted answer?  :)

If you want to change this, post a comment using the 'Object' button.  That will cancel the pending closure so that you can correct it.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Objecting because I think the wrong comment was selected inadvertently.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.