split a string and output to a listbox in ms access 2007

if i have a textbox with contents开发者_如何学JAVA like "the big brown fox jumped over the lazy dog", how do i split it and put the contents in a listbox like this:

0,the

1,big

2,brown

3,fox

4,jumped

5,over

6,the

7,lazy

8,dog

PLz help, newbie

You could use the Split() function. Something like this:

  Public Function SplitToListBox(ByVal strInput As String) As String
    Dim strTemp() As String
    Dim intCounter As Integer
    Dim strRowsource As String
    Const strQuote As String = """"

    strTemp() = Split(strInput, " ")
    For intCounter = 0 To UBound(strTemp())
      If Len(strRowsource) = 0 Then
         strRowsource = strQuote & Trim(CStr(intCounter)) & strQuote & "; " & strQuote & strTemp(intCounter) & strQuote
      Else
         strRowsource = strRowsource & "; " & strQuote & Trim(CStr(intCounter)) & strQuote & "; " & strQuote & strTemp(intCounter) & strQuote
      End If
    Next intCounter
    SplitToListBox = strRowsource
  End Function

Now, you'd then need a listbox defined with two columns, and you'd want to set the widths on those columns appropriately (0.5";1" works if you want to see both; 0";1" works if you want the first column to be hidden (though it will be the bound column if you don't change the default properties). You also need to set the RowSourceType property to "Value List".

One caveat:

There's a hard limit on the length of the Rowsource property when it's a Value List. I can't remember the exact number, but it's somewhere upward of 2000 characters. If you need more than that, then I'd suggest you convert the code that creates the list to a custom function. There are instructions on how to do this in the help for combo-/listboxes.