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]

I use code to loop through the selected items of the first list box to build a WHERE CONDITION to filter the Row Source of the second list box. I set this up to run in the after update of the first list box.

If you have more questions, consider providing more information about your Row Sources and relationships.

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

Duane,

Not sure what informaion you need. The Multiple select control has one column. I can select multiple items from that column. I need to pass those items to the second Select Controls underlying query to filter the list it will show.

If you have sample code that will do this...can you show me. Also, I have read that each selection in a multiple select list control triggers the "After Update" event...so how can your code work. I am using the "Extended" multi-sellect property for the multi select controls.

Thanks,

 

[puforee]

This is the current code I use in another application that opens a form based on a multiple select list control.
The names of controls would change and instead of opening a form I would like it to requery the second multiple select list box.
Do you have code you can show me as a substitutiojn within the code below? I would like to not use a button...is there a event that would be triggered with I tab of the first list control?

Open Form Button: EmployeeSelectOpenFrmButton
On click event:

Private Sub EmployeeSelectOpenFrmButton_Click()
On Error GoTo Err_EmployeeSelectOpenFrmButton_Click
    
    Application.Echo False
    Dim strWhere As String
    strWhere = "1=1 "
    strWhere = strWhere & BuildIn(Me.List14, "[Name (Last, First, MI)]", "'")
    DoCmd.OpenForm "Employee Frm", acNormal, , strWhere, acEdit, acNormal
    DoCmd.Close acForm, "Employee Selection", acSaveNo
    Application.Echo True

Exit_EmployeeSelectOpenFrmButton_Click:
    Exit Sub

Err_EmployeeSelectOpenFrmButton_Click:
    MsgBox Err.Description
    Resume Exit_EmployeeSelectOpenFrmButton_Click
'....
End Sub

Additionsl information. This is the current code I use in another application that opens a form based on a multiple select list control.

 

[Andrzejek]

puforee,

it looks to me all your logic of getting selected items from the list box is in your function [tt]BuildIn[/tt], which you did not show us. I would assume it looks something like this (example done in Excel VBA, sorry):

Private Function BuildIn(ByRef lst As MSForms.ListBox) As String
Dim s As String

For i = 0 To lst.ListCount - 1
    If lst.Selected(i) = True Then
        s = s & lst.List(i) & vbNewLine
    End If
Next i

BuildIn = s

End Function

If I am correct, you may use logic like this to create [tt]strWhere[/tt] for the query for your second list box.

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]

Pufuree,

You haven't provided any control names or Row Source properties. I think the BuildIn() function is something that I wrote or at least I have created a function with the same name.

Assuming the Northwind sample database with a multi-select list box for product categories that is used to filter the products list box, you code might look like:

Private Sub lboCategories_AfterUpdate()
    Dim strSQL As String
    Dim strWhere As String
    strWhere = BuildIn(Me.lboCategories, "CategoryID", "")
    strSQL = "SELECT ProductID, ProductName from Products WHERE 1 =1 " & strWhere & " ORDER BY ProductName;"
    Me.lboProducts.RowSource = strSQL
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

Wait a minute. I found this code as a function:

Function BuildIn(lboListBox As ListBox, _
        strFieldName As String, strDelim As String) As String
    'send in a list box control object
    Dim strIn As String
    Dim varItem As Variant
        
    If lboListBox.ItemsSelected.Count > 0 Then
        strIn = " AND " & strFieldName & " In ("
        For Each varItem In lboListBox.ItemsSelected
            strIn = strIn & strDelim & lboListBox.ItemData(varItem) & strDelim & ", "
        Next
        'remove the last ", " and add the ")"
        strIn = Left(strIn, Len(strIn) - 2) & ") "
    End If
    BuildIn = strIn

I forgot this was part of what I used before. So, how would I use this to filter my second list box functionality?

Thanks,

 

[dhookom]

pufuree,
That is the function I wrote. Were you able to implement my suggestion?

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Here is a thread from 2012 where I posted the function.

Is your question answered or do you still need some direction?

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

Question not totally answered I don't think. So, I have the BuiltIn function. How do I attach it to the second list box as criteria? Also, what do I use for a triggered event from list box 1 to cause list box two to be requeried? I don't want to use a button...I would much rather list box 1 event?? tigger the filtering of list box 2. I thought of using After Update...but that triggers after each selection in list box 1. I need a trigger that happens after I am done with list box 1.

Thanks,

 

[Andrzejek]

I don't want to use a button" and
"I need a trigger that happens after I am done with list box 1."

So how your app would know when you are done selecting items from list 1?

"I thought of using After Update...but that triggers after each selection in list box 1" that's what I would use. Every time you make a selection - either you are done with it or not yet - your other list updates. What's wrong with it? No button to click

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]

puforee,
I provided all of the code you need in an earlier reply. You haven't suggested if you even tried it.

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

Please bear with me if I am a little slow. But, I have the code in a Function...but how and where do I call the function to filter the second list box?

 

[dhookom]

Just like the scenario I suggested based on the categories and products, the code would be added to the after update of the first list box. If you can't figure this out you should provide the information I asked for a while back "You haven't provided any control names or Row Source properties."

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

Duane...sorry about the missing data. List box 1 "SelModelDD". Row Source date is currently

SELECT DISTINCT [All Query].[Major Model] FROM [All Query] WHERE ((([All Query].[Requesting Org])=[Forms]![Report Criteria Form]![SeltOrgDD])) ORDER BY [All Query].[Major Model];

List box 2 "SelMedTypDD" Ro Source data ios currently

SELECT DISTINCT UpLoad_Data.[Media Type] FROM UpLoad_Data WHERE (((UpLoad_Data.[Media Type]) Is Not Null) And ((UpLoad_Data.[Major Model])=Forms![Report Criteria Form]!SelModelDD) And ((UpLoad_Data.[Requesting Org])=Forms![Report Criteria Form]!SeltOrgDD) And ((UpLoad_Data.Canceled)=0)) Or (((UpLoad_Data.[Media Type]) Is Not Null) And ((UpLoad_Data.[Requesting Org])=Forms![Report Criteria Form]!SeltOrgDD) And ((UpLoad_Data.Canceled)=0) And ((Forms![Report Criteria Form]!SelModelDD) Is Null)) Or (((UpLoad_Data.[Media Type]) Is Not Null) And ((UpLoad_Data.[Requesting Org])=Forms![Report Criteria Form]!SeltOrgDD) And ((UpLoad_Data.Canceled)=0) And ((Forms![Report Criteria Form]!SelModelDD)="")) ORDER BY UpLoad_Data.[Media Type];

Note: these originally were Combo boxes until the users asked for multiple select capability. The above is showing the original setup for them.

Sorry about being a little dense.

 

[puforee]

Duane, I used another DB to analyze BuildIn by adding a message box. So, here is what it produced. 1=1 AND[Name(Last, First, MI] In ('Adams, Dell','Albert,Mark E.') This is a what I got from you quite a while ago and it works. When the message box is closed it opens a form.

In my current DB that I am questioning about, I would expect the results to be something like this. 1=1 AND[SelModelDD] In ('737','747'). How would this be applied to the query for the second list box...just as criteria? Like criteria would = BuildIn?

Thanks,

 

[dhookom]

I'm not fully wrapping my head around your where clause of the second list box. However you might add code to the After Update of SelModelDD like the following which assumes [Major Model] is a text field:

Dim strWhereIn as String
Dim strSQL as String
strWhereIn = BuildIn(Me.SelModelDD,"[Major Model]","'")
strSQL = "SELECT DISTINCT [Media Type] FROM UpLoad_Data WHERE " & _
    "[Media Type] Is Not Null And [Requesting Org]=Forms![Report Criteria Form]!SeltOrgDD AND Canceled=0 " & _
    strWhereIn & _
    " ORDER BY [Media Type]"
Debug.Print strSQL

Me.selMedTypDD.RowSource = strSQL

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

OK..I can work with this. One thing though....I have been told, Google, that on a multi-select list box after update is triggered after each item is selected on the list. Do you see this as a problem? Would it be better to use the Exit event...that way the code is only triggered once. I must say, I have not used this event myself..so I am not sure it would be a viable solution.

Another quick question. Is the data from the first list box held...by that I mean, can I run the BuildIn function more than once. Remember, at the begining, I mentione I have two multiple select list boxes and as you have seen, by the Record source I sent, I am looking at more than the model list box. So after selecting items in list box 1 and selecting items in list box 2 I will be opening (with a button) a report or Form. So, when I push that button can I get the information from both lists boxes to serve a "Where" for a repor/form open?

And I trully do thank you for sticking with me on this.

 

[dhookom]

I much prefer to use the After Update event. If the second row source might contain a million records, I would use an "Apply Filter" button.

Do you want to open the report or form based on the selections in the second list box? I would need more information about the form/report's record source.

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

Sorry, I have been working a few high priority items. But I am back now.

OK...I know you are getting tired of my not understanding so, one more time from the begining:
List box 1 multiple select will drive list box 2's list. So, after finishing list box 1 selections I want list box 2 show a list of items based on the selections of list box 1.

The user will then make one or more selections from multiple select list box 2.

Then a button control will open a report with the criteria includint the results of list box 1 and 2.

I have created the Function BuildIn

Problem 1: I now need to know where the call to that function should be. I believe you are suggesting List Box 1 "After Update". Question, how would this code be set up to filter list box 2.

Problem 2: Let's assume I have solved Problem 1 (with your help). Now I will select 0ne or more from list box two. I need to run the function again...yes? Where do I put the code to run the function for list box 2...on list box 2 "After Update" or???

The next step will be selecting a date range. Start and Stop. I already have this working.

Problem 3: I now need to open a report with a button. The report query criteria will be based on all the selections including the two list boxes.

So how does the button code get the list box 1 and list box 2 information to open the form. I have shown the current code above using single select combo boxes. I have now change the combos to the two lists boxes but left the names the same.

Again, thanks for staying with me on this.