Multiselect Multiple Listboxes

[Deepak.Doddagoudar]

Hello Everyone,


I have 4 list boxes : Machine, Machine System, Machine SubSystem and Components with multiple selection enabled to "Simple" for all. Each list box is hierarchically related, with MAchine on top and Components at the bottom. I also have a button to select for each listbox. Everything is working fine for me. The value I select in Machine gives me the values in the Machine System and the values I select in the MAchine System gives me the value for Machine Sub Subsytem and so on. The row source for the Machine listbox is Machine Table and the row sources for the other list boxes is the Value List.The problem is without selecting the values in the MAchine listbox all values in the other listboxes are getting displayed after clicking their respective select button. How do I eliminate this?
I am new also new to VBA. Please Help.

Regards
Deepak

[orange]

I suggest you review Cascading comboboxes.
There also several youtube videos on this subject.

[Deepak.Doddagoudar]

Thank you for the reply but that is a combo box with no multiple selection. In my case the from contains list boxes with all "multiselection" enabled.

[orange]

Please give us the requirement that needs cascading Listboxes multiselect .
I'm not saying it doesn't apply, but most situations involve cascading combos.

Here is a link that you may find interesting.

[kd2017]

The row source for the Machine listbox is Machine Table and the row sources for the other list boxes is the Value List.

Something seems strange to me that the other listboxes row sources would be value lists, but I don't know your requirements.

The problem is without selecting the values in the MAchine listbox all values in the other listboxes are getting displayed after clicking their respective select button. How do I eliminate this?

What is are the respective select button's supposed to do? Are they supposed to filter the rows in the listboxes themselves? Or filter the data in a query?

I assume after each click you'd need to loop through each listbox to determine what's selected and build a query filter.
https://stackoverflow.com/questions/19551754/how-do-i-return-multi-select-listbox-values-into-a-sentence-using-word-vba

[Deepak.Doddagoudar]

Hello,
When I open the form all four listboxes are visible. The values in the first listbox i.e Machine are visible. I select any value in it and click the button, respective related data gets selected in the second list box. This is as per my requirement. But also without selecting the values from the first Machine listbox if I click the button all its related values are displayed in the Machine system (second listbox). In short the second, third and fourth listbox is showing their all respective values without selecting any from the first listbox

[orange]

It sounds like you may be building a dynamic value list rather than related tables to perform lookup which are the row sources of the combos.

[kd2017]

You said the row source of listboxes 2,3 and 4 are value lists, is this correct? Listbox 1 is the only one with an underlying table?

What code have you tried?

When you say

I select any value in it and click the button, respective related data gets selected in the second list box

do you mean that listboxes 1,2,3 should only display rows related to the previous selection or they should display all rows and select related rows?

Im assuming it goes:
1. Make (multi)selection in listbox 1
2. click select 1
3. load related rows in listbox 2 with nothing selected
4. Make (multi)selection in listbox 2
5. click select 2
6. load related rows in listbox 3 with nothing selected
7. Make (multi)selection in listbox 3
8. click select 3
9. load related rows in listbox 4 with nothing selected
10. Make (multi)selection in listbox 4
11. click select 4
12. Profit

Correct?

If that's the case your row source is going to need to be dynamic. Might be easier to use queries as the row sources rather than value lists.

[Deepak.Doddagoudar]

Yes that is right. Listbox 1 row source is the underlying table and 2,3 and 4 has value list row source. Let me make it simple for you, Listbox 1 records are visible. 2,3 and 4 are not visible but when I click the 2 button without selecting the values all records are visible. 2 must only display records related to selection from 1. 3 must display records from displayed records from selection in 1 and so on.
This is the code:Private Sub btnMachine_Click()



Dim intMachineID As Integer
Dim strWhrMachineID As String
Dim strSQLstr As String
Dim rsData As Recordset

Form_frmFILTER.listMachineSystem.RowSource = ""

For intMachineID = 0 To Form_frmFILTER.listMachine.ListCount - 1
If Form_frmFILTER.listMachine.Selected(intMachineID) Then
If strWhrMachineID <> "" Then
strWhrMachineID = strWhrMachineID & " or " & "tblMachineSystem.[MAchine ID] = " & Form_frmFILTER.listMachine.Column(0, intMachineID)
Else
strWhrMachineID = "Where " & "tblMachineSystem.[MAchine ID] = " & Form_frmFILTER.listMachine.Column(0, intMachineID)
End If
End If
Next intMachineID

strSQLstr = "SELECT tblMachineSystem.[Machine System ID], tblMachineSystem.[Machine System], tblMachineSystem.[MAchine ID] " & _
"FROM tblMachineSystem " & _
strWhrMachineID
Set rsData = CurrentDb.OpenRecordset(strSQLstr)

If (rsData.RecordCount <> 0) Then
Do While Not rsData.EOF
Form_frmFILTER.listMachineSystem.AddItem Item:=rsData.Fields(0).Value & ";" & rsData.Fields(1).Value & ";" & rsData.Fields(2).Value
rsData.MoveNext
Loop

If Form_frmFILTER.listMachine.Selected(intMachineID) = 0 Then
Me.listMachineSystem.Value = True
Else
Me.listMachineSystem.Value = False
End If





End If
End Sub

This is the code to filter from 1 to 2. Similar codes from 2 to 3 and 3 to 4.

[Deepak.Doddagoudar]

Thats precisely what i mean. Sorry I did not see your full message. But how to dynamically populate subsequent listbox?

[Deepak.Doddagoudar]

Yeah, that might the case but I do not have any clue how to do it. I just thought that I can write a simple code like if the items selected for list box one is true then value list for list box two is true else false. But that is not working.

[kd2017]

I'm looking at your code and the approach seems fine to me, there must be a bug in the code somewhere. Can you run a debug.print strSQLstr and report the results? I'd suggest you but a breakpoint in the top of the button's code and step through it to watch how everything is going.

On a side note wouldn't it be simpler to set the rowsource = strSQLstr and then requery the listbox instead of then looping through the recordset and adding each row to a value list?

[Deepak.Doddagoudar]

How do I debug it? I am quite new to access and have never debugged.

[kd2017]

Oh it just dawned on me!

The problem is without selecting the values in the MAchine listbox all values in the other listboxes are getting displayed after clicking their respective select button. How do I eliminate this?

That's because in your code if the loop doesn't find a selected list item you essentially no longer have a WHERE clause in your sql statement.

Insert this line between Next intMachineID and strSQLstr = "SELECT tblMachineSystem.[Machine System ID]...

 If strWhrMachineID = "" Then Exit Sub

Should look like

    For intMachineID = 0 To Form_frmFILTER.listMachine.ListCount - 1
        If Form_frmFILTER.listMachine.Selected(intMachineID) Then
            If strWhrMachineID <> "" Then
                strWhrMachineID = strWhrMachineID & " or " & "tblMachineSystem.[MAchine ID] = " & Form_frmFILTER.listMachine.Column(0, intMachineID)
            Else
                strWhrMachineID = "Where " & "tblMachineSystem.[MAchine ID] = " & Form_frmFILTER.listMachine.Column(0, intMachineID)
            End If
        End If
    Next intMachineID
    
    If strWhrMachineID = "" Then Exit Sub
        
    strSQLstr = "SELECT tblMachineSystem.[Machine System ID], tblMachineSystem.[Machine System], tblMachineSystem.[MAchine ID] " & _
                    "FROM tblMachineSystem " & strWhrMachineID
    Set rsData = CurrentDb.OpenRecordset(strSQLstr)

If there are no selections in the first listbox this line will quit the sub leaving listbox 2 empty.

[Deepak.Doddagoudar]

You are an ABSOLUTE GENIUS. Thanks a ton. You are a real Absolute Performer. Cheers