Field display order in MS Access listbox control
Posted on 2003-11-08
Hi, I'm having problems with the field display order in my access list box.
I open a recordset with a SQL query as its source. Then, i set the
listbox.recordset = to the recordset i just opened.
But, the fields appear in the listbox columns in a different order
than in the select statment!
It's so weird.
If i copy (and format) the SQL statement into access's query maker, it
runs fine and the fields are in the correct order.
I'm using ADO objects in access XP to
Has this happened to anyone else? and what can i do to fix it?
The code is below. I know the query is really ugly but i didn't know
how else to do it. A simple left join wouldn't work (it gave me an
error) so i had to use the union.
I think i needed a 'full outer join'.
SQL improvement suggestions are also very welcome (i'm a bit of a
The fields appear in the listbox in this order:
alpha_no, circuit_no, ext_no, pair_no, pair_status, term_name, tn,
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
SQL = "SELECT circuit.circuit_no, circuit.alpha_no, " & _
"circuit.ext_no, circuit.tn_loc, circuit.tn, type.type_name, " & _
"Pair.pair_no , Pair.pair_status, Terminal.term_name " & _
"FROM ((((drops INNER JOIN circuit ON " & _
drops.circuit_no=circuit.circuit_no) " & _
"LEFT JOIN type ON circuit.type_ident = type.type_ident) " & _
"INNER JOIN pair on pair.drop_ident = drops.drop_ident) " & _
"INNER JOIN terminal ON " & _
"pair.term_ident = terminal.term_ident) " & _
"INNER JOIN cable ON " & _
"terminal.cable_ident = cable.cable_ident " & _
"UNION " & _
"SELECT drops.drop_ident, drops.drop_ident, drops.drop_ident, " & _
"drops.drop_ident, drops.drop_ident, drops.drop_ident, " & _
"pair.pair_no, Pair.pair_status , Terminal.term_name " & _
"FROM " & _
((pair LEFT JOIN drops ON pair.drop_ident=drops.drop_ident) " & _
"INNER JOIN terminal ON pair.term_ident=terminal.term_ident) " & _
"INNER JOIN cable ON terminal.cable_ident = cable.cable_ident " & _
"WHERE drops.drop_ident Is Null " & _
"ORDER BY pair.pair_no"
rs.Open SQL, cnn, adOpenStatic, adLockReadOnly
Set FormDisplay.lstTable.Recordset = rs
thanks in advance for your help.