Multiselect list box and having selection stay 1

[VRach02]

Hello
Here is a dilema in my coding. I have three list boxes that populate each other. Ie a choice from a fills listbox b and a choice from b fills list box c. I have multiple selections on list box b and c.

List box b contains a choice of subheading and each subheading give 1 or more paragraph titles in listbox c.

The wat is goes now is that every time a choice is made in lstbox b.. listbox c repopulates and anyold selections that were made disappear.

Does that make any sense? and how could i possibly fix it.
Thanks
Vishal

 

[PaulBricker]

Vishal, if the code is in the AfterUpdate event for Listbox B then it is going to fire each time you select something from ListB which is going to reset the Row Source for List C. What you would have to do is put the code behind a button and then use something like this.

Dim ctl as Control
Dim varItm as Variant
Dim strSQL as String
Set ctl = Me.ListboxC
For each varItm in ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItm) & " Or "
Next
strSQl = Left(strSQL, Len(strSQL) - 4)
strSQL = "Select TableName.FieldName From TableName Where TableName.FieldName = '" & strSQL & "'"
Me.ListboxC.RowSource = strSQL

This should get you close. Try it and post back with specific problems.

Paul

 

[PaulBricker]

I've modified the code a little to account for some syntax problems. Here is the new code that would go behind a button. If you select one value then it will return records for that value. If you go back and select a second value, it will rerun the sql and set the rowsource to each value selected.

Private Sub Command4_Click()
Dim strSQL As String
Dim ctl As Control
Dim varItm As Variant
Set ctl = Me.List0
For Each varItm In ctl.ItemsSelected
strSQL = strSQL & "'" & ctl.ItemData(varItm) & "'" & " Or "
Next

strSQL = Left(strSQL, Len(strSQL) - 4)

strSQL = "Select qryScheduler.RentalDate from qryScheduler Where qryScheduler.OrgName = " & strSQL

Me.List2.RowSource = strSQL

End Sub


Paul

 

[VRach02]

How is this code behind a button going to help the problem.
I still have to choose all the subheading in listboxb before going to the button and see the choice in listboxc?

 

[PaulBricker]

It's possible that I didn't understand the problem. What my solution does is allow the user to select 1 or more selections from a list box and see the results in another list box without the results from the first selection going away. If that isn't the problem, could you restate what is happening and I'll see if something else will help.

Paul

 

[VRach02]

That is currently what i have, but i need the seleections made in the second box to not disappear.

The code seems to show the right data but it does not keep what is selected... selected.


v

 

[PaulBricker]

This should do it. Paste this code behind the button, and just change the two lines where I Set the values ctl and ctl2 to your ListBoxName.

On Error GoTo ErrHandler
Dim strSQL As String
Dim ctl As Control, ctl2 As Control
Dim intCurrentRow As Integer
Dim varItm As Variant
Dim myRow As String
Set ctl = Me.List0
Set ctl2 = Me.List2
For intCurrentRow = 0 To ctl2.ListCount - 1
If ctl2.Selected(intCurrentRow) Then
myRow = myRow & intCurrentRow & ","
End If
Next intCurrentRow
For Each varItm In ctl.ItemsSelected
strSQL = strSQL & "'" & ctl.ItemData(varItm) & "'" & " Or "
Next
strSQL = Left(strSQL, Len(strSQL) - 4)
strSQL = "Select qryScheduler.RentalDate from qryScheduler Where qryScheduler.OrgName = " & strSQL
ctl2.RowSource = strSQL

Dim i As Integer, x As Integer
If myRow = "" Then
Exit Sub
Else

i = 1
x = 1
Do Until x = 0
x = InStr(i, myRow, ",&quot
ctl2.Selected(Mid(myRow, i, x - 1)) = True
myRow = Right(myRow, Len(myRow) - x)
Loop

End If
ErrHandler:
Exit Sub


Paul

 

[VRach02]

Thanks paul
that code really helped..

It solved a lot of problems.

Vishal