Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    journeyman is offline Advanced Beginner
    Windows 11 Access 2016
    Join Date
    Dec 2022
    Posts
    82

    Filtering a subform by query results

    Hi all - Happy Friday.



    I know how to filter for a single value

    Code:
     xyz.form.Filter = "FindMyResult"
     xyz.form.FilterOn = True
    Now for the harder question. I went looking around Google but no joy.

    I have a query that returns multiple results, all of which I would like to be included in the filter (not just one value. All the results will be in the first table

    I thought xyz.form.Filter = "Select * from TblMyTable WHERE [Field1] LIKE '*" & strSearch & "*'" might work. It doesn't.

    I could easy replace the recordset, but I don't wanna, because I'd then have to change it back and mess about with all kinds of other stuff.

    What's the quick n dirty way to filter a recordset based on query results?

    Cheers and advance thanks for helpful response

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    = "Field1 LIKE '*" & strSearch & "*'"

    Otherwise, set form RecordSource property with the complete SQL statement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    journeyman is offline Advanced Beginner
    Windows 11 Access 2016
    Join Date
    Dec 2022
    Posts
    82
    Thanks for the quick response

    I apologise. I don't think I was clear.

    Code:
    = "Field1 LIKE '*" & strSearch & "*'"
    This would presume that I could find a single result within the table using the string as the filter against the table.

    What I'm after if how to resolve against a query of multiple results and filter to map to each record in the query.

    I was trying to avoid changing the recordset of the form due to the amount of coding around it.

    Thanks again

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't understand what you want. If multiple records match that criteria they will display on form.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    journeyman is offline Advanced Beginner
    Windows 11 Access 2016
    Join Date
    Dec 2022
    Posts
    82
    Table one - is a list of thousands of items. eg.
    Item 1
    Item 2
    Item 3
    ...
    ...
    Item 100

    Query One - a list of variable unique items - for example 20 items
    Item 1
    Item 4
    Item 9
    ...
    Item 19

    How to filter for all items in Table One as listed in Query One - i.e. filtering for multiple items.

    Therefore the filter for the query result.

    Of note: Similar functionality is available in a native form by simply clicking the field dropdown and selecting the applicable checkboxes.

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Use the SQL as the recordsource?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    at the moment your requirement is not at all clear - perhaps use some realistic data.

    Would this be more realistic?

    Table one - is a list of thousands of items. eg.
    Item 1
    Item 2
    Item 3
    Item 4
    Item 1
    Item 4
    ...
    Item 100

    Query One - a list of variable unique items - for example 20 items
    Item 1
    Item 4

    (because these items appear more than once)



    or would this

    Table one - is a list of thousands of items. eg.
    Item 1a
    Item 2
    Item 3
    Item 4
    Item 1b
    Item 4
    ...
    Item 100

    Query One - a list of variable unique items - for example 20 items
    Item 1


    (because 1 appears in both items)

  8. #8
    journeyman is offline Advanced Beginner
    Windows 11 Access 2016
    Join Date
    Dec 2022
    Posts
    82
    The idea here is not to use a recordsource to re-popuate the form. the idea is to filter the original data against the results of a query.

    Yes, I get that using a recordsource would be better, and yes, I get that using a filter would normally apply to single item.

    But I want my original data set to return the results of my query.

    Therefore if my query returns:

    Item 1
    Item 2
    Item 3

    Then my table will filter to those items, using only those items as the filter condition.

    I thought something like: Filter = "[Field] WHERE IN Qry.Field" but I don't know the syntax.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Query and form are based on same table?

    Again, either set RecordSource property to query SQL statement or set Filter property to criteria.

    What do you mean by "using a filter would normally apply to single item"? Applying criteria to Filter property will return whatever records meet criteria. Criteria can involve multiple fields, same as query WHERE clause.

    Whatever criteria you use for query should be usable in Filter property.

    Are you trying to filter form in multiple steps? What is the 'other stuff' that would be messed with?

    Suggest you provide db for analysis.



    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    journeyman is offline Advanced Beginner
    Windows 11 Access 2016
    Join Date
    Dec 2022
    Posts
    82
    Never mind. Abandoning the question. I seem not have have made the point clear.

    Thanks for responses.

  11. #11
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    your filter should look something like this
    Code:
     xyz.form.Filter ="[Field1] LIKE '*" & strSearch & "*'" 
     xyz.form.FilterOn = True
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    moke, that was suggested way back in post 2 and rejected.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    But I want my original data set to return the results of my query.
    not tried it but perhaps

    filter="[field1] in (" & sqlToMyQuery & ")"

    Where sqlToMyQuery would be something like

    SELECT Field1 FROM myTable WHERE some criteria

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I tried. It works.

    Just make sure the query pulls only one field.

    I did have issue with one form when I tried to filter with a text field. Very odd. Not a problem with a different form.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Quote Originally Posted by June7 View Post
    moke, that was suggested way back in post 2 and rejected.
    Oops. looks like I didn't hit the post button on an edit to that post for multiple criteria in the filter.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

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

Similar Threads

  1. how to get the results of a query into a subform
    By ManuelLavesa in forum Access
    Replies: 22
    Last Post: 03-15-2018, 12:31 PM
  2. Replies: 5
    Last Post: 11-12-2014, 04:36 PM
  3. Filtering query results
    By jwreding in forum Queries
    Replies: 12
    Last Post: 12-28-2011, 01:45 PM
  4. Query results in subform
    By MWMike in forum Forms
    Replies: 5
    Last Post: 09-28-2010, 05:19 PM
  5. Replies: 0
    Last Post: 03-31-2010, 07:52 AM

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