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

M

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?
 
Can you post the code?
 
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:
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?

meninnet said:
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
Click to expand...
 
Dear all,

It seems that use .Column = rs.GetRows rather than .List = Application.Transpose(rcArray) can solve this problem.
 
Surely, rcArray has already done an rst.GetRows?
 
".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
 
Personally, I always change Nulls on the select statement.
 
You must log in or register to reply here.
Back
Top