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.
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 = TrueASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
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?
Contacts is the name of the table. Anything else I can check/tweak?
ASKER
That works! Thank you very much.
Then could I also have a button to remove the filter that just simply did: Me.FilterOn = False?
Then could I also have a button to remove the filter that just simply did: Me.FilterOn = False?
ASKER
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
Accepted answer: 0 points for UptimeSystems's comment http:/Q_27431827.html#37084124
for the following reason:
ty
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
Generally for a 'filter off' button, you would 1) clear the filter and 2) set filter on to false:
Me.Filter = ""
Me.FilterOn = false
<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.
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.
Objecting because I think the wrong comment was selected inadvertently.
Me.Filter = ("frmCustomersGrid") And (Contacts.ContactShortName
Me.FilterOn = True