Solved

Field display order in MS Access listbox control

Posted on 2003-11-08
14
997 Views
Last Modified: 2013-12-25
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
beginner)

The fields appear in the listbox in this order:
alpha_no, circuit_no, ext_no, pair_no, pair_status, term_name, tn,
tn_loc, type_name

----code:

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

cnn.Open CurrentProject.BaseConnectionString

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

thanks in advance for your help.
0
Comment
Question by:thelastcowboy
  • 4
  • 4
  • 2
  • +3
14 Comments
 
LVL 3

Expert Comment

by:WRNewman
ID: 9709525
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
0
 
LVL 3

Expert Comment

by:vbbuff
ID: 9714438
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
0
 

Author Comment

by:thelastcowboy
ID: 9715213
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.
0
 

Author Comment

by:thelastcowboy
ID: 9715295
This guy had the same problem:
http://www.experts-exchange.com/Databases/MS_Access/Q_20722738.html

I was trying to avoid looping through all my records.

0
 

Author Comment

by:thelastcowboy
ID: 9715601
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
_______________________
0
 

Expert Comment

by:ghomrigh
ID: 9717141
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
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 29

Expert Comment

by:leonstryker
ID: 9717203
AnnieMod

How about we PAQ/With Refund this question.

Leon
0
 

Expert Comment

by:ghomrigh
ID: 9717266
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
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9717300
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
0
 

Expert Comment

by:ghomrigh
ID: 9717311
...fair enough
0
 

Author Comment

by:thelastcowboy
ID: 9722245
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.

0
 

Expert Comment

by:ghomrigh
ID: 9723582
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
0
 

Accepted Solution

by:
SpazMODic earned 0 total points
ID: 9743183
PAQed, with points refunded (500)

SpazMODic
EE Moderator
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now