Pass your recordset to a function that builds a semi-colon delimited string of all the values in your table, set you listbox's Row Source Type property to Value List and its Column Count property to the number of columns in your table. Then assign the string returned from your string building function to your ListBox's Rowsource property:
Code:
Private Sub cmdFillList_Click()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strList As String
Dim strSQL As String
strSQL = "SELECT CategoryID, CategoryName FROM tblCategories " _
& "ORDER BY CategoryName"
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open strSQL, cnn, adOpenStatic
strList = BuildString(rst)
lstListBox.RowSource = strList
lstListBox.Selected(0) = True
rst.Close
End Sub
Private Function BuildString(rst As ADODB.Recordset) As String
Dim strReturn As String
Dim varItems As Variant
Dim x As Integer
Dim y As Integer
' GetRows method returns a two-dimensional array
With rst
varItems = .GetRows(.RecordCount)
End With
For x = LBound(varItems, 2) To UBound(varItems, 2)
For y = LBound(varItems, 1) To UBound(varItems, 1)
strReturn = strReturn & varItems(y, x) & ";"
Next y
Next x
BuildString = strReturn
End Function
HTH,
Bob
ListBox--Displayyyyyyyyyyy
