5 hours ago: Our file storage provider is running maintenance today. If any videos don't load, wait a minute, refresh the page, and try again. Sorry for any inconvenience.  Dismiss
Free Lessons
Fast Tips
Topic Index
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
Home > TechHelp > Directory > Access > Filter < Favorite Customers | Troubleshooter >
Back to Filter Records    Comments List
Cant Filter by CRITERIA Upload Images   Link   Email  
Brian Hartwig       
3 years ago
I follow the guidelines, and the template as downloaded. However, when I try to filter by {CRITERIA} it won't work. When I set a static filter from the method you showed by right clicking in the fields, it works.

I can get the Criteria input box and filter button to show up, even with naming the button as cmdFilter. I have also named my combo box as cmbFilter.


*([frmInventoryList].[DESCRIPTION] Like '*ear*') works, and populates 75 of 260 records.
*([frmInventoryList].[DESCRIPTION] Like '*{CRITERIA') doesn't work, as it doesn't want to "ApplyFilter". Hmmm...

Again, your training guides have been very helpful, quick tips and extended member only cuts, in expediting a refresh of a database for a local jeweler.

Thank you to anyone that has a better idea of what might be going on. And if needed I will create a video recording of the unique issue, as I have xSplit at my disposal.
Adam Schwanz             
3 years ago
I haven't brushed up on what the example in the class is, but I believe your syntax is wrong. Try Like '*{CRITERIA}*'
Adam Schwanz             
3 years ago
*([frmInventoryList].[DESCRIPTION] Like '*{CRITERIA}*')
Brian Hartwig       
3 years ago
Not sure why "}" didn't show up on the main post, unless I manually deleted it in editing the line, but it's been in my table, and the filter button won't do anything, despite what the example references, with my basic naming modifications. Maybe Richard will have an idea down the road when he gets to it. In the meantime, I will set and test a few others with static names for stone type, metal type.

Concept, needs to be easy to operate by the end user, which typically involves the extra coding behind the scenes, and like the "Concatenation / Double Double Quotes" video tutorial, there is likely some simple thing I am overlooking.
Scott Axton            
3 years ago
Brian,  since you are a Platinum member, you can download the sample db provided ( if you haven't already).
Compare what you are doing vs the code in the sample. In think you are correct in that you are probably missing something simple.

Are you getting errors or just not getting the expected results?  Stating "it won't work" doesn't tell us anything.  Help us help you.  When you have an issue be as detailed as you can of what happens.
Brian Hartwig       
3 years ago
I already had, well before postiopied. I had copied the code over, and only changed the command and combo box names where needed to reflect the connection. I will create a video of the issue. As stated, the copied over codes implement by full code in the [FilterText] field, versus using the {CRITERIA} reference code in place of the actual alphanumeric search. When I set a message box to show "Me.Filter" after clicking submit, it comes up blank with {CRITERIA} set. There's a disconnect somewhere in the way this database happens to be setup vs his templates.
Brian Hartwig       
3 years ago
***I already had, well before posted. ....
Kevin Robertson             
3 years ago
Double check your code. There may be a typo in the InStr function and therefore not recognising {Criteria}.
Brian Hartwig       
3 years ago
If there was a typo in the InStr function, than it wouldn't be working in the template from Richard, as I did a direct copy and paste of the code.

And after review between his and mine, no difference between the two.

Starting to think that the Advanced Filter method is not meant to be for this database; well, at least for the Inventory form.

Though "Silly rabbit, Trix are for kids!". LOL I finally found the simple oversight: the "events", though created *after* the text box and command button, decided to not be connected. :P DOH!
Scott Axton            
3 years ago
See Forum Posting Rules
I appreciate the offer of making a video but there isn't a way to post that here.  And for various reasons (security, virus. etc) Richard doesn't allow external links in the forum.  
Brian Hartwig       
3 years ago
Actually, there is a minor loophole... uploading a screenshot. :P Yet I know from earlier searches and reading of replies, that it's not an option to post links directly in the replies, or the original post in text format.
Scott Axton            
3 years ago
OK I just re-watched the video to see what you might be running into.  

We are at the point where it would be useful for you to post some screenshots of your code and filters.  You can do that by going to your original post and clicking the Upload Images in the upper right hand corner.

My gut feeling is that you are running into the same issue of converting the quotes. Like in the video at 25:00 approximately.
Scott Axton            
3 years ago
Just to play devil's advocate here a little bit and help you think ahead.  
You mentioned, "needs to be easy to operate by the end user, which typically involves the extra coding behind the scenes".

Have you given any thought on how you are going to let the user change over the "hard filter" to a "criteria filter" ?

The way it is right now, it looks to me like every time the jeweler wants a new query with criteria you are going to get a phone call.  
Because you have to edit the filter to replace the {Criteria} in the correct place in the table - which you don't want you user having direct access to.

I can also see the case where your combo box will be littered with tons of similar filters with one minor tweak.  
Who gets to add a filter?  How do you delete the filters no longer needed?
Does your form have a Reset button that clears the filter for them?  You don't want them to start freaking out that their data is "gone".

Scott Axton            
3 years ago
Point is you need to try and anticipate issues and handle them in advance.  Guaranteed once you think your code is unbreakable the Customer will figure out a way to break it.
Brian Hartwig       
3 years ago
It's still in development. The other drop-down menus referenced were for initially testing the code, and are set to clear the values of the others, so you only focus on the active category (field). Though I will likely try to enhance that with basic color changes: fill, text, etc.

Yet until the hashing out of going over the code, on video that is only only HDD, and then the reference to look at inStr, did I realize that the 2 key components, were not connected to their respective events, thus going nowhere.

As for adding a reset button in addition to just clicking the traditional "filtered" at the bottom of the window, and the option to add custom filters, it is in the planning stages. Oone step at a time to try and not overload my brain. So now that the box and button are working as intended, it's time to put in the [Save Filter] button.

As for thinking code is unbreakable, never have thought that way from playing around with it since the we got our first computer Christmas 1986.
Brian Hartwig       
3 years ago
So in conclusion, as the original topic has been resolved: A lesson learned....

By creating the form 1st, having set the proper "active" names of the labels, text boxes, etc, and then going directly to the Code builder, MS Access (365 in my case), will NOT automatically link to the set event, even when the code is created by using the built-in drop-down lists to make sure the spelling is correct. This break is also noted by most, when we change the active name(s), and then still see the old one listed.

After all the troubles, hashing out comparing code between two DBs, knowing the only difference was manually changed, associated "active" names of the combo box and command button,  the only thing I had not done until about 1am PT, was to "properly" look at the events tab to see if they had been set. Oops! So once that was taken care of, voila!

On one side, I'm surprised it doesn't re-/connect automatically, yet this may just be a security measure on Microsoft's part.
Richard Rost             
3 years ago
Yeah, that's one of my pet peeves with Access. If you rename an object like a button, if there is VBA code associated with that button, you have to manually edit your code for the new name. Yet, Access will rename tables, queries, forms, etc.
Brian Hartwig       
3 years ago
Come to find out, on rare occasion will it actually add it to the intended control event. I noted when I copied a button that I set to get the 4 window dimensions to popup in 1 message box, and then copy the code to the respective form for setting static windows, voila! It connected, as the name copied over from "form to form", unlike the standard renaming when copied within the same form or sub-form, which can also break the event code links. :P

Anyway, I now have my Filter options working, and instead of using the same table for the custom entries, for safety, I added a respective "custom" table for each need, that I joined by Union Query. I also figured out how to show the number of records in the filter view, as well as the total number of "actual" records in the table, so that I can keep the traditional *tiny* bar hidden. I have also added an option to "view" the active filter, as well as reset it.

Thank you all, once again, for the guidance.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Filter Records.


The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 7/14/2024 11:12:33 AM. PLT: 2s