Populating a multi column listbox with ADO Recordset
 

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 [table];"
Set cnt = New ADODB.Connection
With cnt
..Provider = "Microsoft.Jet.OLEDB.4.0"
..Properties("Data Source") = "F:\TESTADO.mdb"
'.Properties("Jet OLEDB:Database 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

Populating a multi column listbox with ADO Recordset
 

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

"Paul Faulkner" wrote in message
...
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 [table];"
Set cnt = New ADODB.Connection
With cnt
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source") = "F:\TESTADO.mdb"
'.Properties("Jet OLEDB:Database 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

Populating a multi column listbox with ADO Recordset
 

Bob,

Thank you, that worked perfectly.

Paul

"Bob Phillips" wrote:

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

"Paul Faulkner" wrote in message
...
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 [table];"
Set cnt = New ADODB.Connection
With cnt
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source") = "F:\TESTADO.mdb"
'.Properties("Jet OLEDB:Database 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