Results 1 to 4 of 4
  1. #1
    Bcanfield83 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    81

    Question Filter Records In Continuous Form Where [Field] In (Results From Another Query)

    Hi All,
    I'm attempting to create a function where a Continuous Form would be filtered based on the results of another query. Here's the basic overview:


    -Form contains records assigned to a specific UserID in a Combo Box (Assigned UserID). Each UserID can have 1 or more records assigned to them
    -A separate query uses Count to show any UserID's that have only 1 record assigned.

    I'm trying to filter the records in the form for only the UserID's that have 1 record assigned. I think I'm somewhat close, using Allen Brown's function located here: http://allenbrowne.com/func-concat.html. This is populating the strUsers variable with a list of the UserID's, e.g.: "User1, User2, User3, User4".
    However, when the filter is applied, it prompts for a parameter value for each of those users in strUsers. If I click "OK" to each parameter value box, it turns the filter on but returns zero results. Thoughts?

    Code:
    strUsers = ConcatRelated("Assigned_UID", "qryMyForm_UIDs_With_Only_1_Record_Assigned")
    
    
            
        Me.Filter = "[Assigned UserID] IN (" & strUsers & ")"
        Me.FilterOn = True

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If the values are text like your example, each needs to be in quotes:

    'user1','user2'
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Bcanfield83 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    81
    Quote Originally Posted by pbaldy View Post
    If the values are text like your example, each needs to be in quotes:

    'user1','user2'
    Thank you! That was my problem. I updated the code to include a single quote at the beginning and end of the ConcatRelated function and then also added it as part of the (optional) delimitter argument.
    The filter is now working as intended

    Code:
    strUsers = "'" & ConcatRelated("Assigned_UID", "qryMyForm_UIDs_With_Only_1_Record_Assigned", , , "','") & "'"
            
        Me.Filter = "[Assigned UserID] IN (" & strUsers & ")"
        Me.FilterOn = True

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 19
    Last Post: 06-02-2019, 12:06 PM
  2. Replies: 1
    Last Post: 12-25-2018, 03:59 PM
  3. Replies: 5
    Last Post: 12-09-2015, 11:28 AM
  4. Filter email field on continuous form gives error
    By Ruegen in forum Programming
    Replies: 7
    Last Post: 10-10-2013, 09:31 PM
  5. Make Table query from results on Continuous Form
    By lewis1682 in forum Programming
    Replies: 3
    Last Post: 09-13-2013, 07:03 AM

Tags for this Thread

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