Populate List Box with an array

[steveroberts]

I want users to be able to select values in a multi column list box and pass them to a blank list box so as to build up their own values in their own list box from the base list. I then want to create a table from their selections.

I can get the results into a suitable array, but cannot pass the array to the blank list box. I've tried:

Forms!FormName!ListBox.List = Array (as works in Excel' VBA environment), but it Access's object model doesn't recognise the list property. I've tried substituting 'List' with RowSource etc, but nothing seems to work.

I really don't want to have the array writing to a temporary table which then drives the list box.

Any ideas?

 

[LittleSmudge]

I think this is what you need :-


Private Sub FillListBox(intArrayLowerBound As Integer, intArrayUpperBound As Integer)

Dim intIndex As Integer

For intIndex = intArrayLowerBound To intArrayUpperBound
If IsNull(ListBox.RowSource) Then
ListBox.RowSource = TheArray(intIndex)
Else
ListBox.RowSource = ListBox.RowSource & ";" & intArray(intIndex)
End If
Next

End Sub

This assumes TheArray is a Module level variable Array


QED ?

G LS

 

[steveroberts]

Thanks for that. I've ended up using a custom function set as the List Box's RowSourceType (ie. RowSourceType - ListArray) which seems to work pretty well:

Public Function ListArray(fld As Control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant
Dim ReturnVal As Variant
If IsEmpty(myArray) Then
ReDim myArray(iArrayRow, iArrayCol)
End If
Select Case code
Case acLBInitialize
ReturnVal = True
Case acLBOpen
ReturnVal = Timer
Case acLBGetRowCount
ReturnVal = UBound(myArray, 1)
Case acLBGetColumnCount
ReturnVal = UBound(myArray, 2)
Case acLBGetColumnWidth
ReturnVal = -1
Case acLBGetValue
ReturnVal = myArray(row, col)
End Select
ListArray = ReturnVal
End Function


Thanks again.