Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2006
    Location
    Seattle
    Posts
    4

    Unanswered: Field Order of a Recordset

    Hello all,

    I am working in MS Access 2003, trying to set the Recordset of a few combo boxes and listboxes on a form.
    I am experimenting with a simple table (tblMain01) in a seperate .mdb file. In it are the fields: ID, Text, Number, and Date. Each field formated to hold those values.
    The .udl controls the connection so I can modify the datasource without putting the system in an admin state and users don't have downtime and network drive letter changes from location to location. No table or queries are linked or stored in the front-end.

    Code:
    Code:
    Private Sub Form_Load()
        Dim cnn As New ADODB.Connection
        Dim rst As New ADODB.Recordset
        
        cnn.Open ("file name=C:\udls\ComboBox.udl")
        
        rst.Open "SELECT * FROM tblMain01", cnn, 1, 2
        Set Me.cbxUnsorted.Recordset = rst
        Set Me.lbxUnsorted.Recordset = rst
        rst.Close
        
        rst.Open "SELECT ID, Text, Number, Date FROM tblMain01", cnn, 1, 2
        '---Query from datasource returns same result---
        'rst.Open "qryITND", cnn, 1, 2
        Set Me.cbxITND.Recordset = rst
        Set Me.lbxITND.Recordset = rst
        rst.Close
    
        rst.Open "SELECT Number, Date, ID, Text FROM tblMain01", cnn, 1, 2
        'rst.Open "qryNDIT", cnn, 1, 2
        Set Me.cbxNDIT.Recordset = rst
        Set Me.lbxNDIT.Recordset = rst
        rst.Close
    
        cnn.Close
    
    exit Sub
    The issue I'm having is that no matter how I change the field order, the recordset ALWAYS returns the fields in this order: Date, ID, Number, Text
    Is this hard-coded into Access or ADO?

    Anybody have ANY thoughts?

    If you read this and know ANYTHING about the question, or a work-around PLEASE respond (even if you know there is no way to accomplish what I'm trying to do). If I can not solve this I have to rebuild, and I am on a pretty tight timeline.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Couldn't you modify the following:

    rst.Open "SELECT ID, Text, Number, Date FROM tblMain01", cnn, 1, 2

    to

    rst.Open "SELECT ID, Text, Number, Date FROM tblMain01 Order By MyFieldName", cnn, 1, 2

    If not, I'm guessing it might have something to do with your *.udl connection.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Sep 2006
    Location
    Seattle
    Posts
    4
    That's not the order I'm trying to manipulate.

    ODER BY in the SQL statement changes the sort of the records in the Recordset, not the Field order displayed in the list box.

  4. #4
    Join Date
    Sep 2006
    Location
    Seattle
    Posts
    4
    Sorry, Paul I meant to thank you for the quick response.

    Here is a sample of the concept I am trying to workout.
    It is the fe, be and udl file.
    Modify the paths for your own system...
    Attached Files Attached Files

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I misunderstood. Sorry about that.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Never used the List.recordset property to fill a list before, but I find it acts just as you say. Very bizar.

    However, if there is not solution to this (anybody ?), then I suggest you change the Row Source Property to Value List and do soemthing like this

    Code:
    Private Sub Form_Load()
        Dim rs As New ADODB.Recordset
        Dim i As Integer
        Dim j As Integer
        Dim strList As String
        
        rs.Open "SELECT  id, Name ,Units, Hours FROM tblData", CurrentProject.Connection, adOpenStatic, adLockReadOnly
    
        For i = 1 To rs.RecordCount
            For j = 0 To rs.Fields.Count - 1
                strList = strList & ";" & rs(j)
            Next j
            rs.MoveNext
        Next i
        strList = Mid(strList, 2)  'REMOVE LEADING COLON
    
        lstTest.RowSource = strList
        rs.Close
        Set rs = Nothing
    End Sub

    Seems to work !


    MTB

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well that surprises me. I've never had that problem.
    Have you seen the linked doc?
    http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx
    Might give you a clue.

    I will have a play with your attachment....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Gotta set your cursor location fella - this has got to run at the client:
    Code:
    rst.CursorLocation = adUseClient
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Sep 2006
    Location
    Seattle
    Posts
    4
    Thank you both.
    Mike, I will experiment with your code and see what I can do with it.
    Pootle, I thought there had to be a simple way to do this. It works perfectly.

  10. #10
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Having been inspired to rummage round the properties/methods of recordset by pootle's link, there is an even simpler way to extract/constuct a string for the value list ie

    Code:
    Private Sub Form_Load()
        Dim cn As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim strList As String
    
        Set cn = CurrentProject.Connection
        
        rs.Open "SELECT id, Name, Units, Hours FROM tblData", cn, adOpenKeyset, adLockReadOnly
        
        strList = rs.GetString(adClipString, , ";", ";")
    
        rs.Close
        Set rs = Nothing
        cn.Close
        Set cn = Nothing
        
        lstTest.RowSource = strList
    
    End Sub
    Hope this is not teaching you how to suck eggs, but it was new to me.

    Although it does take some of the fun out of programming having it all done for you, a bit like driving an automatic car !



    MTB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •