Solved: Listbox multiselect issue

[dhartford]

I ha've 2 listbox on a form. User makes multiple items in box1 and info related to items will show on box2.

What event can I use to make this happen? AfterUpdate event doesn't work since it fires every time user selects a item. Do I have to use OnClick event of a separate command botton to run the code? I really don't want to do that.

Thanks in advance.

[CreganTur]

The issue you're running into here has to do with the fact that listboxes that allow multiple selections behave very differently than other listboxes.

When the multi-select property is set to None, then you can refer to the value of the listbox object, and have the selected value returned.

Once you change multi-select property to another value, the behavior of the listbox changes. In order to return the value of the selected items you have to loop through the entire listbox's rowsource and check to see if a row was selected or not. The following For...Next loop accomplishes this and prints the selected items to the Immediate Window (ListTest is the name of the listbox I used):
[VBA]Dim litem As Long
For litem = 0 To Me.ListTest.ListCount - 1
If Me.ListTest.Selected(litem) = True Then
Debug.Print Me.ListTest.ItemData(litem)
Me.ListTest.Selected(litem) = False
End If
Next[/VBA]

What does this mean for you? I'm guessing that your rowsource for your second listbox is dependant on a SQL string that uses a WHERE clause that's dependant on the value of listbox 1. If you want to allow your users to select multiple items, then you're going to need to figure out how you can add the individual selected items into your SQL statement.

[dhartford]

What does this mean for you? I'm guessing that your rowsource for your second listbox is dependant on a SQL string that uses a WHERE clause that's dependant on the value of listbox 1. If you want to allow your users to select multiple items, then you're going to need to figure out how you can add the individual selected items into your SQL statement.

GeganTur,

Thanks for the advice. This is exactly I'm going to do. Use the loop to extract selections in listbox1 to a string which will form a WHERE clause to build a SQL string for the rowsource of listbox 2. Does this make sense?

What event would be good? afterupdate or onclick? or something else?

Thanks again.

[CreganTur]

Personally I'd go with a Click event on a command button because using an Update event with a multi select listbox might cause problems- specifically the code firing before the user has made all selections.

[dhartford]

I'm having trouble to figure out how to add the individual selected items into my SQL statement.

My where clause should look like: where company = A or company = B or company = C ......, depending on how many item selected by user.

Dim strWhere as stringDim i as integerFor i = 0 To List1.ListCount - 1     If ListTest.Selected(litem) = True Then         strWhere = strWhere & List1.ItemData(i) 'company name      End If Next 

I tried strWhere = "'or company ='" and whatever I can think,none of them works yet.Thanks

[CreganTur]

Try this- it will concatenate everything the way you want.

You may need to change it so it adds double quotes instead of single quotes.

[vba]

Dim litem As Long
Dim strWhere As String
Dim i As Integer
i = 1
For litem = 0 To Me.ListTest.ListCount - 1
If Me.ListTest.Selected(litem) = True Then
if i = 1 Then
strWhere = "company = '" & Me.ListTest.ItemData(litem) & "'"
ElseIf i > 1 Then
strWhere = strWhere & " Or company = '" & Me.ListTest.ItemData(litem) & "'"
End If
i = i + 1
Me.ListTest.Selected(litem) = False
End If
Next

[/vba]

NinjaEdit: I added in a counter variable that has a value of 1 the first time a listbox value is added into the string you're building for your WHERE clause. The reason for this is that the very first time you don't need an 'Or'. This is so you could have a valid SQL string created with only a single listbox choice.

For every other iteration, the 'Or' keyword will be added so that you end up with a valid SQL string.

[dhartford]

Try this- it will concatenate everything the way you want.

You may need to change it so it adds double quotes instead of single quotes.

.

CraganTur,

It works very well. I can't thank you enough for helping me out. This forum is great. I'll change the title to "solved".

Thanks again.

[dhartford]

I ha've 2 listbox on a form. User makes multiple items in box1 and info related to items will show on box2.

What event can I use to make this happen? AfterUpdate event doesn't work since it fires every time user selects a item. Do I have to use OnClick event of a separate command botton to run the code? I really don't want to do that.

Thanks in advance.

[CreganTur]

To mark a thread as solved you need to click on Thread tools near the top of your screen. Select "Mark As Solved" and submit- that will mark the thread as solved.

Glad I could help