Populating a multi column listbox with ADO Recordset

[Paul Faulkner]

I'm trying to populate a multi column listbox with a ADO recordset I have
retrieved from Access, the code I have so far is adapted from the Microsoft
site and works fine for a single column, but I cannot get the Age records to
show, can anybody help?

The code so far;


Public Sub PopulateControl()

Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT Name, Age FROM

;"
Set cnt = New ADODB.Connection
With cnt
..Provider = "Microsoft.Jet.OLEDB.4.0"
..Properties("Data Source") = "F:\TESTADO.mdb"
'.Properties("Jet OLEDBatabase Password") = "REDUNDANT"
..Open
End With

Set rst = New ADODB.Recordset
With rst
..CursorType = adOpenKeyset
..LockType = adLockOptimistic
..Open strSQL, cnt
End With

' Moves to the first record in the record set.
rst.MoveFirst

Do Until rst.EOF
UserForm1.ListBox1.AddItem rst!Name
rst.MoveNext
Loop

UserForm1.Show

rst.Close
cnt.Close

End Sub

thanks,
Paul

 

[Bob Phillips]

Paul,

Try this

With UserForm1.ListBox1
.AddItem rst!Name
.List(.ListCount - 1, 1) = rst!Age
End With


Don't forget to set the columncount of the listbox

 

[Paul Faulkner]

Bob,

Thank you, that worked perfectly.

Paul

Paul,

Try this

With UserForm1.ListBox1
.AddItem rst!Name
.List(.ListCount - 1, 1) = rst!Age
End With


Don't forget to set the columncount of the listbox

--
HTH

Bob Phillips

I'm trying to populate a multi column listbox with a ADO recordset I have
retrieved from Access, the code I have so far is adapted from the Microsoft
site and works fine for a single column, but I cannot get the Age records to
show, can anybody help?

The code so far;


Public Sub PopulateControl()

Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT Name, Age FROM

;"
Set cnt = New ADODB.Connection
With cnt
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source") = "F:\TESTADO.mdb"
'.Properties("Jet OLEDBatabase Password") = "REDUNDANT"
.Open
End With

Set rst = New ADODB.Recordset
With rst
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open strSQL, cnt
End With

' Moves to the first record in the record set.
rst.MoveFirst

Do Until rst.EOF
UserForm1.ListBox1.AddItem rst!Name
rst.MoveNext
Loop

UserForm1.Show

rst.Close
cnt.Close

End Sub

thanks,
Paul