Transpose data from a sql query (Access) to fill listbox in form (VBA)

Question

Hello,

I have a VBA form with a listbox and a button that fills it from an access database (in the same folder)

Private Sub searchDB(strSQL As String)

    Dim objConn As Object

    Dim objRecSet As Object

    Dim varSQLdata As Variant

 'get data from Access database

    Set objConn = CreateObject("ADODB.Connection")

    objConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & wbCashRegister.Path & "\ccPOSDB.accdb"

    Set objRecSet = objConn.Execute(strSQL)

    On Error Resume Next

    varSQLdata = objRecSet.GetRows()

    Me.lbxProductList.Clear

    Me.lbxProductList.List = Application.Transpose(varSQLdata)

'close connections

    objRecSet.Close

    Set objRecSet = Nothing

    objConn.Close

    Set objConn = Nothing

End Sub

When I press the button, If there if only 1 record returned from a query, the data is not properly filled (horizontally) in my 3-column listbox. The single  record is displayed vertically in the first column and occupies 3 rows. What could I do to correct it?

Also, If there is no data that match the query I get an error in VBA. I have put "On Error Resume Next" (in bold), is it the best way to handle it?

The code works but could you tell me if there is anything that should be done differently?

Thanks!

Answer 1

I found another way of doing it if anyone is interested, this way even if 1 record is returned it will be displayed correctly in the listbox

Private Sub searchDB(strSQL As String)

    Dim objConn As Object

    Dim objRecSet As Object

    Dim i As Integer

 'get data from Access database

    Set objConn = CreateObject("ADODB.Connection")

    objConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & wbCashRegister.Path & "\ccPOSDB.accdb"

    Set objRecSet = objConn.Execute(strSQL & "ORDER BY ProductName ASC")

    Me.lbxProductList.Clear

    With Me.lbxProductList

        While objRecSet.EOF = False

            .AddItem

            .List(i, 0) = objRecSet.Fields(0).Value

            .List(i, 1) = objRecSet.Fields(1).Value

            .List(i, 2) = Format(objRecSet.Fields(2).Value, "#,##0.00")

            objRecSet.MoveNext

          i = i + 1

        Wend

    End With

    objRecSet.Close

    Set objRecSet = Nothing

    objConn.Close

    Set objConn = Nothing