Populating Listbox using VBA code

[vbc22]

Hi there,

What am I missing that causes the listbox columns to be out of sequence from the SQL I used in retrieving a recordset, which I set to the listbox? Clearly in my SQL, it's the NameID that comes before the Name field, but after it has run, it's the reverse.

I'm using Access 2003.

Private Sub Form_Load()

    ' load names in listbox
    Dim sql As String, cn As ADODB.Connection, rs As ADODB.Recordset
    
    sql = "SELECT NameID, Name FROM tNames"
    
    Set cn = New ADODB.Connection
    Set cn = getConn    ' call to get connection string.
    
    Set rs = New ADODB.Recordset
    rs.Open sql, cn, adOpenKeyset
    
    ' Set up listbox control.
    Me.lstNames.ColumnCount = 2
    Me.lstNames.ColumnHeads = True
    Me.lstNames.BoundColumn = 1
    Me.lstNames.ColumnWidths = ".5in;1in"
    Me.lstNames.RowSourceType = "Table/Query"
    
    Set Me.lstNames.Recordset = rs
    
    rs.Close: Set rs = Nothing
    
End Sub

Any ideas?

 

[vbc22]

Ok, I just tinkered with the SQL.

sql = "SELECT Name, NameID AS Ename FROM tNames"

It looks on the surface that the headers are sorted, which sets up the order in which they will appear in the listbox.

That's kind of restrictive, if you have header names that are not alphabetically and sequentially in sync.

Am I missing something here?