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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>