Followup questions regarding "Populate Multi-Column Listbox With Data From Access"

meninnet

New member
Joined
Feb 14, 2012
Messages
6
Reaction score
0
Points
0
Followup questions regarding "Populate Multi-Column Listbox With Data From Access"

Populate Multi-Column Listbox With Data From Access



Thanks for the code! It's very simple and intuitive!
However, it seems that the code is not work when there is only 1 record in the table?
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,941
Reaction score
0
Points
36
Excel Version(s)
O365
Can you post the code?
 

meninnet

New member
Joined
Feb 14, 2012
Messages
6
Reaction score
0
Points
0
In fact I use the code by Ken, as I cannot paste link right now

Code:
Option Explicit
'Set reference to the Microsoft ActiveX Data Objects x.x Library!

'Global constants required
Const glob_sdbPath = "C:\Temp\FoodTest.mdb"

'Const glob_sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & glob_DBPath & "';" 'For use with *.accdb files
Const glob_sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & glob_sdbPath & ";" 'For use with *.mdb files

Private Sub PopulateSuppliers()
'Author : Ken Puls 
'Macro Purpose: Populate the listbox with all values from the Access database

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 tblSuppliers.SupplierName, tblSuppliers.SupplierNumber " & _
"FROM tblSuppliers ORDER BY tblSuppliers.SupplierName;"

'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 = 2
.List = Application.Transpose(rcArray)
.ListIndex = -1
End With

'Close ADO objects
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing
End Sub
 
Last edited by a moderator:

meninnet

New member
Joined
Feb 14, 2012
Messages
6
Reaction score
0
Points
0
For your information, in my recordset, I extract 7 columns, I don't know whether it affect or not.
In addition, if there is NULL data in the recordset, it will display "Type Mismatch" error, is it the expected behavior?

In fact I use the code by Ken, as I cannot paste link right now

Option Explicit
'Set reference to the Microsoft ActiveX Data Objects x.x Library!

'Global constants required
Const glob_sdbPath = "C:\Temp\FoodTest.mdb"

'Const glob_sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & glob_DBPath & "';" 'For use with *.accdb files
Const glob_sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & glob_sdbPath & ";" 'For use with *.mdb files

Private Sub PopulateSuppliers()
'Author : Ken Puls
'Macro Purpose: Populate the listbox with all values from the Access database

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 tblSuppliers.SupplierName, tblSuppliers.SupplierNumber " & _
"FROM tblSuppliers ORDER BY tblSuppliers.SupplierName;"

'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 = 2
.List = Application.Transpose(rcArray)
.ListIndex = -1
End With

'Close ADO objects
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing
End Sub
 

meninnet

New member
Joined
Feb 14, 2012
Messages
6
Reaction score
0
Points
0
Dear all,

It seems that use .Column = rs.GetRows rather than .List = Application.Transpose(rcArray) can solve this problem.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,941
Reaction score
0
Points
36
Excel Version(s)
O365
Surely, rcArray has already done an rst.GetRows?
 

meninnet

New member
Joined
Feb 14, 2012
Messages
6
Reaction score
0
Points
0
".List = Application.Transpose(rs.GetRows)"-----(1)
"
.Column = rs.GetRows"------------------------(2)

(2) is better than (1) because
- It resolve the display problem in listbox if recordset only has 1 record
- It won't have Type Mismatch problem when fields in recordset is null
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,941
Reaction score
0
Points
36
Excel Version(s)
O365
Personally, I always change Nulls on the select statement.
 
Top