Oh my!!! It worked It seems Access will be my new "toy"
Thank you very much!!! I really appreciate the time you spent...
Gracias!
no problem
If anyone cares at this point, I believe that 2 steps are missing from the original macro. Once it ran, the form was filtered. Running the macro against a previously filtered form can cause this. If you had applied the Company filter, then closed the form, reopened then applied the status filter, you likely would have found it worked. I don't use macros either, but I think if you preceded the ApplyFilter with ShowAllRecords, then followed the filter with Requery, it would have worked. In common language, the steps would be
- remove any existing filter
- apply the filter
- requery the form
Those who use vba and not macros (me included) are not used to this behavior. The vba me.filteron seems to be akin to the requery command for macros.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
I'll throw in my $0.02 worth.
I agree with Ajax about you having non-normalized table(s).
Watch out for reserved words.
Field names (actually ANY object name) like "Name", "Type" "Date", "Description" and "Time" are examples of reserved words. They can cause you big headaches.
Here is a list of reserved words I reference: http://allenbrowne.com/AppIssueBadWord.html
"ID" is a poor name for a field, especially if every table has a field named "ID". It is not descriptive
You might also see:
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp
Object names should be only letters and numbers (exception is the underscore). NO spaces, punctuation or special characters.
Do not begin an object name with a number.
I never use
macros,
Look-up fields (different than look up tables) (see The Evils of Lookup Fields in Tables ),
Multi-value fields or
calculated fields in tables.
Also see http://access.mvps.org/access/tencommandments.htm
Hi Micron,
Thank you very much for the reply. If it's not such a big trouble, can you explain it in more details using the code I sent? I mean I understood what you said but how this translates to the macro code I used?
Hi ssanfu,
Thank you very much for the reply. This is very interesting, will take a look at it
I'll throw in my $0.02 worth.
I agree with Ajax about you having non-normalized table(s).
Watch out for reserved words.
Field names (actually ANY object name) like "Name", "Type" "Date", "Description" and "Time" are examples of reserved words. They can cause you big headaches.
Here is a list of reserved words I reference: http://allenbrowne.com/AppIssueBadWord.html
"ID" is a poor name for a field, especially if every table has a field named "ID". It is not descriptive
You might also see:
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp
Object names should be only letters and numbers (exception is the underscore). NO spaces, punctuation or special characters.
Do not begin an object name with a number.
I never use
macros,
Look-up fields (different than look up tables) (see The Evils of Lookup Fields in Tables ),
Multi-value fields or
calculated fields in tables.
Also see http://access.mvps.org/access/tencommandments.htm
Hi Micron,
Thank you very much for the reply. If it's not such a big trouble, can you explain it in more details using the code I sent? I mean I understood what you said but how this translates to the macro code I used?
If anyone cares at this point, I believe that 2 steps are missing from the original macro. Once it ran, the form was filtered. Running the macro against a previously filtered form can cause this. If you had applied the Company filter, then closed the form, reopened then applied the status filter, you likely would have found it worked. I don't use macros either, but I think if you preceded the ApplyFilter with ShowAllRecords, then followed the filter with Requery, it would have worked. In common language, the steps would be
- remove any existing filter
- apply the filter
- requery the form
Those who use vba and not macros (me included) are not used to this behavior. The vba me.filteron seems to be akin to the requery command for macros.
Here's a screen shot of the extra macro steps I'm talking about. The Where clause used for the filter is, I believe, the same pattern as your original. Maybe the ShowAllRecords step is not required because I have the Requery step - I didn't test that. I can tell you it works, but not without the Requery.
Code:[tblProducts].[PRIMARY] Like "*" & [Forms]![frmProducts]![txtsearch] & "*" Or [tblproducts].[secondary] Like "*" & [Forms]![frmProducts]![txtsearch] & "*"
So it's like this? Or PRIMARY + SECONDARY must be replaced as well with something?
Code:[COMPANY].[PRIMARY] Like "*" & [Forms]![qr-BWTS]![txtsearch] & "*" Or [STATUS].[SECONDARY] Like "*" & [Forms]![qr-BWTS]![txtsearch] & "*"
Here's a screen shot of the extra macro steps I'm talking about. The Where clause used for the filter is, I believe, the same pattern as your original. Maybe the ShowAllRecords step is not required because I have the Requery step - I didn't test that. I can tell you it works, but not without the Requery.
Code:[tblProducts].[PRIMARY] Like "*" & [Forms]![frmProducts]![txtsearch] & "*" Or [tblproducts].[secondary] Like "*" & [Forms]![frmProducts]![txtsearch] & "*"
Sorry to have confused you. Primary and Secondary are the field names in my tblProducts table and I'm saying that other than that, it looks to be the same construct as your original filter statement:
[COMPANY] Like "*" & [Forms]![qr-BWTS]![Text87] & "*" Or [STATUS] Like "*" & [Forms]![qr-BWTS]![Text87] & "*"
[PRIMARY] Like "*" & [Forms]![frmProducts]![txtsearch] & "*" Or [secondary] Like "*" & [Forms]![frmProducts]![txtsearch] & "*"
except to illustrate that, I removed the table reference here.
My point is that the two macro steps I included made it work, whereas a macro like yours did not when I tested it, even on my own form and filter string. So unless I'm missing something from way back, your original filter should have worked if you added the two macro steps. The only other difference is that I explicitly refer to the table and field, whereas you are just using the field (from the form, I presume). I would say that if you wish to test that theory and find it still doesn't work, add the table reference as did I. If it still doesn't work, I think I'm beat, since it works for me. The only other avenue would be to examine your db, which is probably pointless since you have a solution. However, if you wish to give back to the forum, I think many of us would want to know if adding the macro steps makes it work for you with your original filter statement. As noted, I (and others here) don't use macros, and likely would appreciate knowing the answer.
Last edited by Micron; 11-15-2016 at 11:02 AM. Reason: clarification
WOW! That actually worked
I'm a bit confused though because the video I have watched didn't refer to the table, just the field and it worked... Anyway, thank you very much for the help. I think I will stick to VBA and use this instead of macrosCode:[BWTS].[COMPANY] Like "*" & [Forms]![qr-BWTS]![Text87] & "*" Or [BWTS].[STATUS] Like "*" & [Forms]![qr-BWTS]![Text87] & "*"
This can happen when controls have the same names as the fields they are bound to. Can't recall if we know that to be your case or not - just saying.
What's really ironic is that it looks like I had a solution when I was told not to bother because I obviously didn't.
Good luck with your project!
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Thank you very much