Field display order in MS Access listbox control

Your order by clause is there os could it be something as simple as setting the sorted property of the list box to false.

Assuming that the simple answer is not it, then probably something in your SQL call is causing the order by clause not to work.

The best way is to rem out lines of your SQL call to condense it to something very simple
Select a few fields from the table with pair_no in it and order that. If that works, slowly add back more complexity and find what clause causes it.

Will

Dear cowboy
It would be helpful if you give the tables in your database along with their fields & what you want to acheive

regards vbbuff

Thanks for your responses.

Will, in Access listboxes don't have the .sorted property. It might have something to do with the .bound column but i don't think it does. And doesn't the .sorted sort the rows alphabetically? I can sort the row order fine (with ORDER BY) but how do i specify the column order?

In the initial post i should have stated that this (below) query displays properly when i connect to an oracle database

    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 circuit, type, subtype, drops, pair, terminal, cable " & _
          "WHERE circuit.type_ident = type.type_ident(+) and " & _
            "drops.circuit_no = circuit.circuit_no(+) and " & _
            "pair.drop_ident = drops.drop_ident(+) and pair.term_ident = terminal.term_ident(+) and " & _
            "terminal.cable_ident = cable.cable_ident(+) " & _
          "ORDER BY pair.pair_no"
         
    rs.Open SQL, cnn, adOpenDynamic, adLockOptimistic
    Set FormDisplay.lstTable.Recordset = rs

but when use the other query on a local access database, the listbox gets messed up.
Since the original question, i've noticed that a combobox is having problems too.

vbbuff, there are 12 tables and about 60 fields total. If you really think it would help, i'll post everything. My gut feeling is that this is a problem with the listbox control and connections that use the .baseconnection string. (or possibly queries with unions)

All i'm trying to do is display a few fields in a listbox to emulating the legacy lotus notes appplication that i'm replacing. The database deals with phone connections in an office and this query will show each pair on a cable and what it's connected to.

Thanks again in advance.

This guy had the same problem:
https://www.experts-exchange.com/questions/20722738/Setting-listbox-column-numbers-by-ordinal-position-of-a-recordset.html

I was trying to avoid looping through all my records.

Okay, so i solved my own problem.

A simple cnn.CursorLocation = adUseClient did the trick.

It also helped my combobox column order and now it autocompletes too.

Thanks will and vbbuff for your help.

The complete (working) function looks like
_______________________

        Dim cnn As New ADODB.Connection
        Dim rs As New ADODB.Recordset

        cnn.Open CurrentProject.BaseConnectionString

        '********* LINE THAT FIXES IT
        cnn.CursorLocation = adUseClient
        '************************

        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

        rs.close
        cnn.close
_______________________

thelastcowboy,

I saw your comment in my initial question.  I thought I'd respond here.  I could never resolve it the way I wanted, but I found two work-around solutions.  Two solutions to my problem are:

1) I found that I could format the columns as I needed by using recordsets in XML formatted files.  ALso, using "Persisted" XML files in a listbox has been, atleast from my experience, more stable than using a "disconnected" recordset (especially if you allow the user to select values that you are pushing into a list).  As a matter of fact, I hear a lot of DBA's complain about finding that Access cannot do anything right, or that it messes stuff up... nonsense.  I have been able to find a solution to everything I need to do except using "disconnected" recordsets in lisboxes, and setting the ordinal position of a recordset- those have been my only complaints.  I can do almost anything else I need to do.  Its not the right solution for every problem, but it can do a lot more than what people give it credit.

(You mentioned that you use adUseClient for the cursor, but you would not if you were going to get the recordset in XML file format).

If you want to know more about using XML files as recordsets, I'll tell you how to do this if you ask and say please... :)

2)  Also, I made a function that takes a recordset, and spits out a String that I use in the RowSource property of a listbox.  It formats the string to fit into a listbox the way that I want it to work.  The only thing is that you have to set the number of columns.  (This has become my preferred wy of dealing w/this; however, I would not recommend this if you are dealing with large recordsets, I'd use the XML file approach instead because working with strings in this manner can be time-consuming.)

So, in your code do this:

2a) get your recordset
Dim rst as adodb.recordset
Set rst = new Adodb.recordset
' do what ever you need to do to get your recordset
.....Connections SQL string, I'd say you'd put the cursor location of your connection, as far as I understand, on the server unless you would let the user make changes with the recordset.

Then when you are going to populate your list... make a with block that uses the OutputString function

2b) set up your with-block similar to this...

with lst_MyList
.ColumnCOunt = X 'Number of columns you need to keep
.BoundColumn = Y 'The ordinal position of the column that you want to be bound
.RowSource = OutputString(YourRecordset) ' call the function that accepts the recordset you ' using(below)
End with

---------------------------------------------------------------------
I recommend putting it into a class, but you may use it however you want:
It reads across a recordset, and then down.  I'm not sure that this is self-explainatory, but you seem like you are at a level where you can figure out how it works.  If not, I'll help you with it.
---------------------------------------------------------------------
2c)
Public Function OutPutString(rstObj as Adodb.Recordset) As String
Dim intI As Integer
Dim strIn As String
Dim strOut As String
Dim intColumns as Integer

intColumns = rstObj.Fields.Count - 1 'Get the number of columns in your recordset

On Error GoTo Clear_Null

rstObj.MoveFirst                                   ' <-Start the recordset from the top
    Do Until rstObj.EOF = True                     '<- Loop until we hit bottom
        For intI = 0 To intColumns    '<- For each column in recordset
            strIn = Chr(34) & rstObj.Fields(intI) & Chr(34) & ";"  '<- = "Value(n)";
                strOut = strOut & strIn         '<- ="Value(1)";"Value(2)";"Value(n...)"
        Next intI
rstObj.MoveNext                                    '<- Move to next record
    intI = 0                                    '<- Reset intI to first row
    Loop                                        '<- Do it again.

OutPutString = strOut

Debug.Print OutPutString

Exit Function
Clear_Null:
    Err.Clear
        strIn = Chr(34) & Chr(34) & ";"   'If it is null value, continue without an error
    Resume Next
End Function
---------------------------------------------------------------------

-Hope this helps, this problem gave me a headache.  I hope it can relieve yours.

-Greg

AnnieMod

How about we PAQ/With Refund this question.

Leon

Leon,

I'd say that is a good idea, but I just responded (just 7 minutes ago) with a potential solution to his problem.  He refered me to this question this moring threw a question that I had previously asked, and said our problems were similar.  So I took the liberty to help him.    

-Greg

Greg,

thelastcowboy posted his solution to his problem at "11/10/2003 10:40AM EST".  Your solutions, no matter how valid, came in at "11/10/2003 02:18PM EST", which is several hours later.  The final dission is up to thelastcowboy and the modirators, but IMO thelastcowboy is deserves a refund.

Leon

...fair enough

Greg,

Thanks for your comprehensive response. I like the solutions you've come up with but since it's working right now, i'm not going to change it. If i run into trouble later, i'll think about learning XML.

I really appreciate the time you took to write everything out, but since my answer is easier to implement in my application, i'm going to pursue a refund.

thelastcowboy,

Well, I'm glad you have everything going.  If it ain't broke, don't fix it.... I'm not worried about the points.

You don't need to learn XML to learn how to use them as recordsets.  Just look up "Persisted recordsets" in your help menue of the VBA editor, and it'll give you the syntax you need to use them.

-Good luck

Greg