Populating List Box from SQL Server
 

How do i populate a listbox from a recordset returned from SQL server. The
recordset will have three fields. I can populate the listbox from a single
field using transpose to swap the data around from getrows. But how do i do
this with a recordset of multiple fields ?

Populating List Box from SQL Server
 

Listbox1.ColumnCount = 3
Listbox1.List = Application.Transpose( your record set)

--
Regards,
Tom Ogilvy


"John Peach" wrote in message
...
How do i populate a listbox from a recordset returned from SQL server. The
recordset will have three fields. I can populate the listbox from a single
field using transpose to swap the data around from getrows. But how do i
do
this with a recordset of multiple fields ?

Populating List Box from SQL Server
 

That doesn't work, I get a Type Mismatch error, here my coe if that helps:
Dim cn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim vArray() As Variant

Dim sql As String

Set cn = New ADODB.Connection

cn.ConnectionString = "Provider=SQLOLEDB.1;Password=lizarduser;Persi st

Security Info=True;User ID=lizarduser;Initial Catalog=Thorn_Lizard;Data

Source=UKSPTHDB01"

cn.Open

sql = "SELECT Name AS [Company Name], PostCode AS [Post Code],

PostCodeSuffix AS Suffix "

sql = sql & "FROM TblBranch INNER JOIN TblCompany ON TblBranch.CompanyId =

TblCompany.Id "

sql = sql & "WHERE (PostCodeSuffix LIKE '%crown%') OR (PostCode LIKE

'%crown%') OR (Name LIKE '%crown%')"

Set rs = New ADODB.Recordset

rs.Open sql, cn, adOpenStatic, adLockReadOnly

If Not (rs.BOF And rs.EOF) Then

vArray = rs.GetRows

rs.Close

ListBox1.ColumnCount = 3

ListBox1.List = Application.Transpose(vArray)


End If


"Tom Ogilvy" wrote in message
...
Listbox1.ColumnCount = 3
Listbox1.List = Application.Transpose( your record set)

--
Regards,
Tom Ogilvy


"John Peach" wrote in message
...
How do i populate a listbox from a recordset returned from SQL server.
The
recordset will have three fields. I can populate the listbox from a
single
field using transpose to swap the data around from getrows. But how do i
do
this with a recordset of multiple fields ?

Populating List Box from SQL Server
 

If you array is larger than 5461 elements, then you would get that error.

If not, it should work.

If it is, then you will have to loop and transpose it yourself or use the
column method.

try

listbox1.Column = vArray

as a demonstration:

Private Sub UserForm_Initialize()
Dim v(1 To 3, 1 To 5)
For j = 1 To 3
For i = 1 To 5
v(j, i) = i & "," & j
Next
Next
ListBox1.ColumnCount = 3
ListBox1.Column = v
End Sub


--
Regards,
Tom Ogilvy


"John Peach" wrote in message
...
That doesn't work, I get a Type Mismatch error, here my coe if that helps:
Dim cn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim vArray() As Variant

Dim sql As String

Set cn = New ADODB.Connection

cn.ConnectionString = "Provider=SQLOLEDB.1;Password=lizarduser;Persi st

Security Info=True;User ID=lizarduser;Initial Catalog=Thorn_Lizard;Data

Source=UKSPTHDB01"

cn.Open

sql = "SELECT Name AS [Company Name], PostCode AS [Post Code],

PostCodeSuffix AS Suffix "

sql = sql & "FROM TblBranch INNER JOIN TblCompany ON TblBranch.CompanyId =

TblCompany.Id "

sql = sql & "WHERE (PostCodeSuffix LIKE '%crown%') OR (PostCode LIKE

'%crown%') OR (Name LIKE '%crown%')"

Set rs = New ADODB.Recordset

rs.Open sql, cn, adOpenStatic, adLockReadOnly

If Not (rs.BOF And rs.EOF) Then

vArray = rs.GetRows

rs.Close

ListBox1.ColumnCount = 3

ListBox1.List = Application.Transpose(vArray)


End If


"Tom Ogilvy" wrote in message
...
Listbox1.ColumnCount = 3
Listbox1.List = Application.Transpose( your record set)

--
Regards,
Tom Ogilvy


"John Peach" wrote in message
...
How do i populate a listbox from a recordset returned from SQL server.
The
recordset will have three fields. I can populate the listbox from a
single
field using transpose to swap the data around from getrows. But how do
i
do
this with a recordset of multiple fields ?