How can I incoporate both lists in my filter?

Lopez, Ahiezer 236 Reputation points
2021-11-10T21:58:23.847+00:00

I have two lists in the frmEmployeeExtendedDIg_Both form that I want to use to filter the SearchingJobs form. As of right now the database only uses the Project Numbers list but I don't know how to incorporate the projects (right hand list) as well.

https://www.dropbox.com/sh/5db4azc0y6t0na9/AAAaIAYBza7zwB2VVvazQrPOa?dl=0

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
898 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Ken Sheridan 2,846 Reputation points
    2021-11-15T01:26:09.78+00:00

    I see that you are using the form from my CorrelatedLists demo. In the demo the filtering of the rows in the form is done by means of parameters in the form's RecordSource query:

    SELECT Customers.ID, [First Name] & " " & [Last Name] AS Customer,
    Products.[Product Name], SUM([Order Details].Quantity) AS Quantity
    FROM Products INNER JOIN ((Customers INNER JOIN Orders
    ON Customers.ID = Orders.[Customer ID]) INNER JOIN [Order Details]
    ON Orders.[Order ID] = [Order Details].[Order ID])
    ON Products.ID = [Order Details].[Product ID]
    WHERE (InParam(Customers.ID,Forms!frmMultiSelectCorrelated!txtCustomerList) = TRUE
    OR Forms!frmMultiSelectCorrelated!txtCustomerList IS NULL)
    AND (InParam(Products.ID,Forms!frmMultiSelectCorrelated!txtProductList) = TRUE
    OR Forms!frmMultiSelectCorrelated!txtProductList IS NULL)
    GROUP BY Customers.ID, [First Name] & " " & [Last Name], [Last Name], [First Name], Products.[Product Name] ORDER BY [Last Name], [First Name], Products.[Product Name];

    Because the IN operator does not accept a parameter as its value list the above query uses the following module, originally published by Microsoft, to simulate the use of the IN operator:

    Option Compare Database
    Option Explicit

    '============================================================
    ' The InParam() function is the heart of this article. When
    ' the query runs, this function causes a query parameter
    ' dialog box to appear so you can enter a list of values.
    ' The values you enter are interpreted as if you
    ' had entered them within the parentheses of the In() operator.
    '============================================================
    Function InParam(Fld, Param)

    Dim stToken As String
    'The following two lines are optional, making queries
    'case-insensitive
    Fld = UCase(Fld)
    Param = UCase(Param)
    
    If IsNull(Fld) Then Fld = ""
        Do While (Len(Param) > 0)
        stToken = GetToken(Param, ",")
        If stToken = LTrim$(RTrim$(Fld)) Then
            InParam = -1
            Exit Function
        Else
            InParam = 0
        End If
    Loop
    

    End Function

    Function GetToken(stLn, stDelim)

    Dim iDelim As Integer, stToken As String
    
    iDelim = InStr(1, stLn, stDelim)
    
    If (iDelim <> 0) Then
        stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
        stLn = Mid$(stLn, iDelim + 1)
    Else
        stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
        stLn = ""
    End If
    
    GetToken = stToken
    

    End Function

    The two parameters referenced by the form's query are hidden text boxes in the form. These are populated by the AfterUpdate event procedures of the two multi-select list boxes:

    Private Sub lstCustomers_AfterUpdate()

    Dim varItem As Variant
    Dim strCustomerList As String
    Dim strSQL As String
    
    With Me.lstCustomers
        If .ItemsSelected.Count <> 0 Then
            For Each varItem In .ItemsSelected
                strCustomerList = strCustomerList & "," & .ItemData(varItem)
            Next varItem
            ' remove leading comma
            strCustomerList = Mid(strCustomerList, 2)
            ' assign list to hidden control
            Me.txtCustomerList = strCustomerList
            Me.txtProductList = Null
    
            strSQL = "SELECT Products.ID,[Product Name], SUM(Quantity) " & _
                "FROM Products INNER JOIN (Orders INNER JOIN [Order Details] " & _
                "ON Orders.[Order ID] = [Order Details].[Order ID]) " & _
                "ON Products.ID = [Order Details].[Product ID] " & _
                "WHERE [Customer ID] IN(" & strCustomerList & ") " & _
                "GROUP BY Products.ID,[Product Name] " & _
                "ORDER BY Products.[Product Name]"
            Me.Requery
        End If
    
        Me.lstProducts.RowSource = strSQL
    
    End With
    

    End Sub

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    Private Sub lstProducts_AfterUpdate()

    Dim varItem As Variant
    Dim strProductList As String
    
    With Me.lstProducts
        If .ItemsSelected.Count <> 0 Then
            For Each varItem In .ItemsSelected
                strProductList = strProductList & "," & .ItemData(varItem)
            Next varItem
            ' remove leading comma
            strProductList = Mid(strProductList, 2)
            ' assign list to hidden control
            Me.txtProductList = strProductList
            Me.Requery
        End If
    End With
    

    End Sub

    The first of the above two procedures also builds and assigns an SQL statement to the RowSource property of the second list box to correlate it with the first, so that it lists only those products ordered by the selected customers.


  2. Ken Sheridan 2,846 Reputation points
    2021-11-15T17:31:51.343+00:00

    The code for the button to open the report in the MultiSelect demo is:

    Private Sub cmdOpenReport_Click()

    Dim varItem As Variant
    Dim strEmployeeIDList As String
    Dim strProjectIDList As String
    Dim strCriteria As String
    Dim ctrl As Control
    
    Set ctrl = Me.lstEmployees
    
    If ctrl.ItemsSelected.Count > 0 Then
        For Each varItem In ctrl.ItemsSelected
            strEmployeeIDList = strEmployeeIDList & "," & ctrl.ItemData(varItem)
        Next varItem
    
        ' remove leading comma
        strEmployeeIDList = Mid(strEmployeeIDList, 2)
    
        strCriteria = " Or EmployeeID In(" & strEmployeeIDList & ")"
    
    End If
    
    Set ctrl = Me.lstProjects
    
    If ctrl.ItemsSelected.Count > 0 Then
        For Each varItem In ctrl.ItemsSelected
            strProjectIDList = strProjectIDList & "," & ctrl.ItemData(varItem)
        Next varItem
    
        ' remove leading comma
        strProjectIDList = Mid(strProjectIDList, 2)
    
        strCriteria = strCriteria & " Or EmployeeID IN" & _
            "(SELECT EmployeeID FROM ProjectEmployees " & _
            "WHERE ProjectID IN (" & strProjectIDList & "))"
    
    End If
    
    If Len(strCriteria) > 0 Then
        ' remove leading ' or '
        strCriteria = Mid(strCriteria, 5)
    
        DoCmd.OpenReport "rptEmployees", _
            View:=acViewPreview, _
            WhereCondition:=strCriteria
    Else
        MsgBox "No employee(s) or project(s) selected.", vbExclamation, "Warning"
    End If
    

    End Sub

    This differs from the correlated lists demo which I referred to above in that the two list boxes are not correlated, but act independently, so the report will return rows which include any of the selected employees, or any of the selected projects. What the above code does is to loop through each of the list boxes' ItemsSelected collections in the usual way, and build two separate value lists of employees and projects respectively. The two strings are then combined into a single string expression in which the OR operator is used, thus requiring each row returned to match one or both of the two criteria.

    The first criterion is a simple one:

        strCriteria = " Or EmployeeID In(" & strEmployeeIDList & ")"
    

    This simply requires the rows returned in the report to include an EmployeeID value from the value list.

    The second criterion is more complex:

        strCriteria = strCriteria & " Or EmployeeID IN" & _
            "(SELECT EmployeeID FROM ProjectEmployees " & _
            "WHERE ProjectID IN (" & strProjectIDList & "))"
    

    In this, if we work outwards, the subquery returns a set of EmployeeID values from the ProjectEmployes table where the ProjectID value matches any of the values IN the value list produced by looping through the lstProjects list box's ItemsSelected collection. The first IN operator is then applied to the values returned by the subquery, restricting the rows returned to those with an EmployeeID value which matches any in the set returned by the subquery. The resulting expression is combined with that for employees, using the OR operator, so that the final expression restricts the rows returned to those with an EmployeeID value in either list.

    What you have to do is substitute code which returns and combines two criteria expressions based on the selections in your two controls. To do this you need to study and understand how my code works. You can then employ that understanding in writing your own code to suit your data. I could of course download your file and write the code for you, but that would be giving you fish, whereas what I'm trying to do is teach you how to fish, which will be of far greater value to you, not only in developing this database, but in further databases which you develop in the future.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.