PDA

View Full Version : Import recordset to a listbox



mdmackillop
01-17-2006, 03:41 PM
Hi all,
I have the following code to import from a recordset (Access) to a single column listbox. How do I need to change this to import to a two column listbox or combobox?
Regards
MD
Set rst = dbs.OpenRecordset(Sql)
Do While Not rst.EOF
Me.ListBox1.AddItem Format(rst("Job No"), "0000") & " - " & rst("Project Title")
rst.MoveNext
Loop

XL-Dennis
01-20-2006, 10:47 AM
Hi :)

Hm, why do You use DAO in the first place?

Below is an example that use ADO and that populate a combobox with data in three columns:


Option Explicit
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Const stSQL As String = "SELECT * FROM Shippers;"
Const stCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Northwind.mdb;" & _
"Persist Security Info=False"
Private Sub UserForm_Initialize()
Dim vaData As Variant
Set cnt = New ADODB.Connection
With cnt
.Open stCon
Set rst = .Execute(stSQL)
End With
'Populate the variant array with the recordset.
vaData = rst.GetRows
With Me.ComboBox1
'This refer to number of columns
.ColumnCount = 3
.Clear
'Populate the list with the variant arrays's data
.List = Application.Transpose(vaData)
.ListIndex = -1
End With

Set cnt = Nothing: Set rst = Nothing
End Sub


Kind regards,
Dennis