Fill Multicolumn List Box Control on Form with Named Range SQL

[justvba]

Hi All,

I have a form called frmRemitSpinner with a listbox control called lstInvoices. I want to populate the entire list box based on a dynamic range InvoiceList but based on some conditions that I define in my SQL.

Now, in the properties of my listbox, I have set it to multi-select, column count 2.

Enclosed is the code I am currently using to fill it but I keep getting an error Run Time '-2147352571 (80020005) "Could not set the list property. Type mismatch"

I know this is because I have a null in the 2nd column but how can I make it say 0 or just leave it blank?

<code class="code-10 nolinks" id="code-10-27963740-1">Public Sub popStandardInvoices()

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim wb As String
Dim i As Integer


'Identify the workbook you are referencing
wb = Application.ThisWorkbook.FullName

'Open connection to the workbook
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "Data Source=" & wb & ";" & _
                  "Extended Properties=""Excel 12.0;HDR=Yes"";"


rst.Open "SELECT distinct(item),price FROM [InvoiceList], cnn, adOpenStatic

rst.MoveFirst
i = 0

With frmRemitSpinner.lstInvoices
    .Clear
    Do
    .AddItem
    .List(i, 0) = rst![item]
    .List(i, 1) = rst![price]
   
    i = i + 1
    rst.MoveNext
    Loop Until rst.EOF 
End With

rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing

End Sub

</code>

 

[Norie]

You could use IsNull to identify Nulls and replace them with an appropriate value.

.List(i, 0) =Iif(IsNull(rst![item), "", rst![item])

.List(i, 0) =Iif(IsNull(rst![price]), 0, rst![price])