Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon  
 
Home > TechHelp > Directory > Access > Filter < Favorite Customers | Not Updateable >
 
Filter Records
By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   15 months ago


Back to Filter Records
 

Cant Filter by CRITERIA Upload Images   Link  
Brian Hartwig 
8 months 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.

i.e.

*([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
8 months 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
8 months ago
*([frmInventoryList].[DESCRIPTION] Like '*{CRITERIA}*')
Brian Hartwig
8 months 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
8 months 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
8 months 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
8 months ago
***I already had, well before posted. ....
Kevin Robertson
8 months ago
Double check your code. There may be a typo in the InStr function and therefore not recognising {Criteria}.
Brian Hartwig
8 months 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
8 months 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
8 months 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
8 months 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
8 months 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
8 months 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
8 months 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
8 months 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
8 months 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
8 months 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, below.
 


Back to Filter Records Comments
 

Start a NEW Conversation
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
Subscribe
 

You may want to read these articles from the 599CD News:

5/27/2022Add Anything
5/26/2022List Items Edit Form
5/25/2022Column Colors
5/24/2022Multi-Table Forms
5/23/2022Variables
5/21/2022Access Developer 39
5/21/2022Access Developer 39 Lessons
5/21/2022Access Developer 39
5/21/2022Select Items in Related Table
5/21/2022Clear List Box
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn