Populating Listbox Header from query to SQl using recordset in Excel UserForm

Article
2018-01-03

Question

_{Wednesday, January 3, 2018 9:07 AM}

Hello All experts

I work on a UserForm and wanna to show SELECT query result in it via query table Headers.

my code is as below:

but on the run I got in the pic:

pls help me

Thank you in advance.

Dim RC, CC As Long
    Dim i, j As Integer
    
    Dim conString As String
    conString = "Driver={SQL Server};Server=192.168.100.2;Database=ODB;Uid=Sa;Pwd=kian;"
    

    Dim query As String
    query = "SELECT     * from dbo.TbVoyage"

    Dim con As Object
    Set con = CreateObject("ADODB.Connection")

    con.ConnectionString = conString
    con.Open
    
    Dim rst As Object
    Set rst = CreateObject("ADODB.Recordset")

    If rst Is Nothing Then
        con.Close
        Set con = Nothing
        MsgBox "RecordSet is empty"
        Exit Sub
    End If
    
    With rst
        .ActiveConnection = con
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Source = query
        .Open
    End With
    
    rst.MoveLast 
    RC = rst.RecordCount 
    CC = rst.Fields.Count 
    rst.MoveFirst 
    
    me. listbox1.ColumnHeads = True
    me. listbox1.ColumnCount = CC 
    
    For i = 0 To CC - 1
        me. listbox1.Column(i, 0).Value = rst.Fields(i).Name
    Next i
    
    While Not rst.EOF
        For i = 1 To RC - 1
            For j = 0 To CC - 1
                me. listbox1.Column(j, i).Value = rst.Fields(j).Value
            Next j
        Next i
        rst.MoveNext 
    Wend

All replies (7)

_{Wednesday, January 3, 2018 10:55 AM ✅Answered | 1 vote}

I have several remarks:

1) Unless you have set a reference to one of the Microsoft ActiveX Data Objects libraries, adOpenDynamic and adLockOptimistic will be undefined, so VBA will use 0.

2) Setting the ColumnHeads property of a list box to True is only useful if the RowSource of the list box is an Excel range. The headers won't be used if you populate the list box any other way.

3) The Column property of a list box is not an object, so you should not use .Value

4) You have to add a row to a list box before you can populate its columns.

5) The line

Dim RC, CC As Long

declares RC as a Variant because its type is not specified explicitly. If you want to declare RC as a Long, use

Dim RC As Long, CC As Long

6) You use While ... Wend to loop through the records, and also For i = 1 To RC - 1 ... Next i. That is double!

Here is a version that should work. It adds the field names in the first row, not in the column headers, since that won't work as mentioned above.

    Dim CC As Long
    Dim i As Long, j As Long

    Dim conString As String
    conString = "Driver={SQL Server};Server=192.168.100.2;Database=ODB;Uid=Sa;Pwd=kian;"

    Dim query As String
    query = "SELECT * FROM dbo.TbVoyage"

    Dim con As Object
    Set con = CreateObject("ADODB.Connection")

    con.ConnectionString = conString
    con.Open

    Dim rst As Object
    Set rst = CreateObject("ADODB.Recordset")

    If rst Is Nothing Then
        con.Close
        Set con = Nothing
        MsgBox "RecordSet is empty"
        Exit Sub
    End If

    With rst
        .ActiveConnection = con
        .CursorType = 2 ' adOpenDynamic
        .LockType = 3 ' adLockOptimistic
        .Source = query
        .Open
    End With

    CC = rst.Fields.Count

    Me.ListBox1.ColumnCount = CC

    Me.ListBox1.AddItem rst.Fields(0).Name
    For j = 1 To CC - 1
        Me.ListBox1.Column(j, 0) = rst.Fields(j).Name
    Next j

    Do While Not rst.EOF
        i = i + 1
        Me.ListBox1.AddItem rst.Fields(0).Value
        For j = 1 To CC - 1
            Me.ListBox1.Column(j, i) = rst.Fields(j).Value
        Next j
        rst.MoveNext
    Loop

    rst.Close
    con.Close

As an alternative, you could place labels above the list box, and set the captions of those labels to the field names.

Regards, Hans Vogelaar (http://www.eileenslounge.com)

_{Wednesday, January 3, 2018 12:17 PM ✅Answered | 1 vote}

No, but you can populate the list box in one go (without the field names). Replace

    Me.ListBox1.AddItem rst.Fields(0).Name
    For j = 1 To CC - 1
        Me.ListBox1.Column(j, 0) = rst.Fields(j).Name
    Next j

    Do While Not rst.EOF
        i = i + 1
        Me.ListBox1.AddItem rst.Fields(0).Value
        For j = 1 To CC - 1
            Me.ListBox1.Column(j, i) = rst.Fields(j).Value
        Next j
        rst.MoveNext
    Loop

with

    Me.ListBox1.List = rst.GetRows

Regards, Hans Vogelaar (http://www.eileenslounge.com)

_{Wednesday, January 3, 2018 1:26 PM ✅Answered}

The following is air code, I can't really test it:

    Me.ListView1.View = lvwReport
    For j = 0 To CC - 1
        Me.ListView1.ColumnHeaders.Add Text:=rst.Fields(j).Name
    Next j

    Do While Not rst.EOF
        Me.ListView1.ListItems.Add Text:=rst.Fields(0).Value
        i = i + 1
        For j = 1 To CC - 1
            Me.ListView1.ListItems(i).ListSubItems.Add Index:=j, Text:=rst.Fields(j).Value
        Next j
        rst.MoveNext
    Loop

Regards, Hans Vogelaar (http://www.eileenslounge.com)

_{Wednesday, January 3, 2018 11:17 AM}

Mr. Hans,

many thanX for your reply which was helpful as always.

just one thing, is there any way to set RowSource to query to get row of recordset?

_{Wednesday, January 3, 2018 12:35 PM}

thank you Mr. Hans

how about list view, could use it instead of list box ?

if yes, could you pls provide code to fill it with header?

rgds

Nima

_{Wednesday, January 3, 2018 1:18 PM | 1 vote}

Yes, that's possible, but the ListView control doesn't work on my PC, so I can't write the code for you. Sorry.

Regards, Hans Vogelaar (http://www.eileenslounge.com)

_{Saturday, January 6, 2018 9:09 AM}

Dear Hans,

Many thanX for your Great Help.

B.RGDS

Nima Iran

Share via

Populating Listbox Header from query to SQl using recordset in Excel UserForm

Question

All replies (7)

Additional resources