Get All Values From A Listbox 1

[jgoodman00]

I have created a series of listboxes which are used for data drill-down in order to select different types of record (based on an ID attained from a tblHierarchy). This consists of Levels1-5. After the user has selected a level 5 classification, an SQL string is constructed which forms the rowsource for another listbox. This listbox selects all HierarchyID's which correspond to the users selections.

The user will not use this final listbox, it is merely to display a list of HierarchyIDs which correspond to their selection. I then want to pass these ID values into a query. However, I cannot see the functionality to enumerate through the listbox values in order to construct a final SQL string to pass into to query. The only enumeration I can see is the ability to enumerate through selected items, which I have used to progressivley narrow down their selections as they select items from each level.

All listboxes are set to Extended multi select.


Any suggestions? James Goodman

 

[tomschafer]

Attach this code to your form.
It will build a comma-delimitted string (strItem) that you can use in your SQL. Note- Change the blue to the name of the List Box you want to get the values of.

Dim varItem As Variant
Dim strItem As String

For Each varItem In Me.List0.ItemsSelected
strItem = strItem & "'" & Me.List0.ItemData(varItem) & "'" & ", "
Next varItem

 

[jgoodman00]

That wont work unfortunately, because this only checks for items within the list which have been selected. Because it is only a reference list, the entries within the list will never be selected.

Hence I need to enumerate through the list items, not the list items which are selected. I have scanned throught the documentation for this & it doesnt look like there is a specific function or method for attaining this.

Any other suggestions? James Goodman

 

[JGillespie]

Use the ListCount property of the ListBox to return the number of items within it. You can then loop through the listbox to return the values like this:

For i = 0 to Me.List0.ListCount - 1
strItem = strItem & "'" & Me.List0.ItemData(i) & "'" & ", "
Next i Jamie Gillespie
j-gillespie@s-cheshire.ac.uk