Results 1 to 8 of 8

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

  2. #2
    Can you post the code?

  3. #3
    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 Bob Phillips; 2012-02-15 at 09:27 AM. Reason: Added code tags

  4. #4
    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?

    Quote Originally Posted by meninnet View Post
    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

  5. #5
    Dear all,

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

  6. #6
    Surely, rcArray has already done an rst.GetRows?

  7. #7
    ".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

  8. #8
    Personally, I always change Nulls on the select statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •