Filter Multiple Select List Control from another multiple select List control

[puforee]

I have a form that I use to set up criteria to return data. There are several selection controls on the form. Two of these are multiple select list Controls. After selection/s have been made on the first multiple select list control I want to filter the secound multiple select control based on the results of the first. I can do the multiple select without problems...but how do I pass the results to the second list control.

I normally disable the second list control until the first is completed (one update event). I then run code to enable the second list control and requery it based on the first list control. I just don't know how to do the requery when there have been multiple selections in the first list control.

Thanks,

 

[dhookom]

1) The code I suggested is in the After Update event of the first list box (SelModelDD) and it will automatically change the row source of the second (selMedTypDD) list box.
2) What do you want to do with the the selected values in the second list box? If it is to filter a report or form, you would use the BuildIn() function prior to the code that opens the report or form. Use the results of the BuildIn() function in the Where Condition of the OpenReport or OpenForm method. The filtering should not have to reference the first list box since its filtering is applied to the second list box.

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

Unfortunately I need the results to both list boxes. Could I use two different named Functions?

 

[Andrzejek]

Something like this?

Function BuildIn2(lboListBox1 As ListBox, _
        strFieldName1 As String, strDelim1 As String, _
        lboListBox2 As ListBox, _
        strFieldName2 As String, strDelim2 As String) As String[green]
    'send in 2 list box control objects, 2 FieldNames, and 2 Delims[/green]
    Dim strIn As String
    Dim varItem As Variant
        
    If lboListBox1.ItemsSelected.Count > 0 Then
        strIn = " AND " & strFieldName1 & " In ("
        For Each varItem In lboListBox1.ItemsSelected
            strIn = strIn & strDelim1 & lboListBox1.ItemData(varItem) & strDelim1 & ", "
        Next[green]
        'remove the last ", " and add the ")"[/green]
        strIn = Left(strIn, Len(strIn) - 2) & ") "
    End If

    If lboListBox2.ItemsSelected.Count > 0 Then
        strIn = strIn & " AND " & strFieldName2 & " In ("
        For Each varItem In lboListBox2.ItemsSelected
            strIn = strIn & strDelim2 & lboListBox2.ItemData(varItem) & strDelim2 & ", "
        Next[green]
        'remove the last ", " and add the ")"[/green]
        strIn = Left(strIn, Len(strIn) - 2) & ") "
    End If

    BuildIn = strIn 

End Function

You pass 6 parameters: ListBox1, FieldName1, Delim1, ListBox2, FieldName2, and Delim2

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dhookom]

There is no reason to create a new function. Each call to the function will return unique results.
You can create a where condition like:

Dim strWhere as String
strWhere = "1 = 1 "
strWhere = strWhere & BuildIn(Me.SelModelDD,"[Major Model]","'")
strWhere = strWhere & BuildIn(Me.selMedTypDD,"[Media Type],"'")
Debug.Print strWhere
DoCmd.OpenForm "Employee Frm", acNormal, , strWhere, acEdit, acNormal

If that doesn't work, reply back with more information about the form/report record source and field data types.


Duane
Hook'D on Access
MS Access MVP