Page 2 of 2 FirstFirst 12
Results 16 to 29 of 29
  1. #16
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397

    hmm looks like you are trying to do a query on a query - I was suggesting this

    Click image for larger version. 

Name:	Capture.JPG 
Views:	14 
Size:	52.9 KB 
ID:	26387

  2. #17
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    Oh my!!! It worked It seems Access will be my new "toy"

    Thank you very much!!! I really appreciate the time you spent...

    Gracias!

  3. #18
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    no problem

  4. #19
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.

  5. #20
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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

  6. #21
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    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?

  7. #22
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    Hi ssanfu,

    Thank you very much for the reply. This is very interesting, will take a look at it

    Quote Originally Posted by ssanfu View Post
    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

  8. #23
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    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?

    Quote Originally Posted by Micron View Post
    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.

  9. #24
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.
    Click image for larger version. 

Name:	macro.jpg 
Views:	12 
Size:	11.1 KB 
ID:	26438

    Code:
    [tblProducts].[PRIMARY] Like "*" & [Forms]![frmProducts]![txtsearch] & "*" Or [tblproducts].[secondary] Like "*" & [Forms]![frmProducts]![txtsearch] & "*"

  10. #25
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    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] & "*"

    Quote Originally Posted by Micron View Post
    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.
    Click image for larger version. 

Name:	macro.jpg 
Views:	12 
Size:	11.1 KB 
ID:	26438

    Code:
    [tblProducts].[PRIMARY] Like "*" & [Forms]![frmProducts]![txtsearch] & "*" Or [tblproducts].[secondary] Like "*" & [Forms]![frmProducts]![txtsearch] & "*"

  11. #26
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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

  12. #27
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    WOW! That actually worked

    Code:
    [BWTS].[COMPANY] Like "*" & [Forms]![qr-BWTS]![Text87] & "*" Or [BWTS].[STATUS] Like "*" & [Forms]![qr-BWTS]![Text87] & "*"
    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 macros

  13. #28
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.

  14. #29
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    Thank you very much

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Why doesn't this DCount() work?
    By tim_tims33 in forum Access
    Replies: 1
    Last Post: 12-16-2014, 07:14 AM
  2. Replies: 5
    Last Post: 04-14-2014, 12:37 PM
  3. Just doesn't seem to work!
    By txmmoore in forum Reports
    Replies: 9
    Last Post: 01-16-2014, 11:39 AM
  4. Replies: 1
    Last Post: 05-02-2012, 11:40 AM
  5. Can Grow doesn't work
    By gg80 in forum Reports
    Replies: 6
    Last Post: 05-13-2011, 07:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums