nuvolari
11-19-2008, 06:51 AM
I'm trying to populate a multi-column listbox with data from Access. The problem is that if the database only has 1 record, it will display each field of the record as a different listbox item.
E.g
Dave
Smith
12/04/1955
instead of "Dave Smith 12/04/1955" as a single listbox item.
'Global constants required
Const glob_sdbPath = "C:\test.mdb"
Const glob_sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & glob_sdbPath & ";"
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rcArray As Variant
Dim sSQL As String
'Set the location of your database, the connection string and the SQL query
sSQL = "SELECT tblPerson.FirstName, tblPerson.LastName, tblPerson.DateOfBirth " & _
"FROM tblPerson;"
'Open connection to the database
cnt.Open glob_sConnect
'Open recordset and copy to an array
rst.Open sSQL, cnt
rcArray = rst.GetRows
'Place data in the listbox
With Me.lbSuppliers
.Clear
.ColumnCount = 3
.List = Application.Transpose(rcArray)
.ListIndex = -1
End With
'Close ADO objects
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing
End Sub
Any help would be appreciated. Thanks
E.g
Dave
Smith
12/04/1955
instead of "Dave Smith 12/04/1955" as a single listbox item.
'Global constants required
Const glob_sdbPath = "C:\test.mdb"
Const glob_sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & glob_sdbPath & ";"
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rcArray As Variant
Dim sSQL As String
'Set the location of your database, the connection string and the SQL query
sSQL = "SELECT tblPerson.FirstName, tblPerson.LastName, tblPerson.DateOfBirth " & _
"FROM tblPerson;"
'Open connection to the database
cnt.Open glob_sConnect
'Open recordset and copy to an array
rst.Open sSQL, cnt
rcArray = rst.GetRows
'Place data in the listbox
With Me.lbSuppliers
.Clear
.ColumnCount = 3
.List = Application.Transpose(rcArray)
.ListIndex = -1
End With
'Close ADO objects
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing
End Sub
Any help would be appreciated. Thanks