Populate listbox from Access

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Populate listbox from Access

Post by adam »

The code below gets me an type mismatch error highlighting the line
.List = Application.Transpose(vaData)

if any column mentioned in the code has empty. How could I overcome it for empty columns?

Code: Select all

 Private Sub CommandButton3_Click()
    
    Dim cnn         As ADODB.Connection
    Dim rst         As ADODB.Recordset
    Dim stConn      As String
    Dim strSQL      As String
    Dim vaData      As Variant
    Dim k           As Long
    
    Set cnn = New ADODB.Connection
    cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
             "Data Source=E:\X\Database.accdb;"
    
    Set rst = New ADODB.Recordset
    
    strSQL = "SELECT [Line Total], [Sub Total]FROM Orders WHERE [Serial No]=" & Me.TexBox1.Value
    rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
   
    
    With rst
        k = .Fields.Count
        
        vaData = .GetRows
    End With
    
    rst.Close
    cnn.Close
    
    With Me
        With .ListBox1
            .Clear
            .BoundColumn = k
            .List = Application.Transpose(vaData)
            .ListIndex = -1
        End With
    End With
    
    Set rst = Nothing
    Set cnn = Nothing
End Sub
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Populate listbox from Access

Post by HansV »

Could you provide a sample database and workbook?
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Populate listbox from Access

Post by adam »

See the image below. Row 2 of subtotal column in empty. and row 4 of Line Total Column in empty. Having this condition I get the error message. But if I have data in the empty row columns the code works fine.

I hope I've made the question clear. Thanks in advance.
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Populate listbox from Access

Post by HansV »

I repeat the question in my previous reply.
Best wishes,
Hans