Setting listbox column numbers by ordinal position of a recordset

hi..

could you post the sql of the query you are using

Avi

Are you getting errors when the code runs?

Right now I'm using:
"Select * from tbl_Collections"

I've also stated each column explicitly (Columns, in order):
Collection_Number, Collection_Name, Collection_Description.

I've now found out that there is a GetRows method that utilizes the Variant data type.  I'd like to avoid using the Variant if at all possible.

I think you need to be re-writing the sql and not trying to assign a recordset object to the rowsource.  The column values are determined by the ordinal position of the columns in the table/query, you can not reset those in code.  They have to be changed by re-writing the rowsource sql.

I'm not using ODBC- that is one of the stipulations of the project, and this table is in another database.  Is there anyway that I can use SQL in the rowsouce without having a link to those tables?

as jack suggested..

try changing the order in the sql of the query itself

say if there are 10 fields in the table : tbl_collections

try this

"Select Collection_Number, Collection_Name, Collection_Description, * From tbl_Collections"

i haven't tried it but i would guess that this should get those 3 columns first and then the rest of the table...and in case if you just need those 3 fields then

"Select Collection_Number, Collection_Name, Collection_Description From tbl_Collections"

try this out
Avi

I have tried that.  Maybe I wasn't clear when I said

"I've also stated each column explicitly (Columns, in order):
Collection_Number, Collection_Name, Collection_Description. "

"I'm not using ODBC- that is one of the stipulations of the project" - Is this a school project??

ghomrigh asked:  "Is there anyway that I can use SQL in the rowsouce without having a link to those tables? "

The tables HAVE to be available to the combobox.  If not ODBC then in a plain vanilla attached table.

You could write the sql to specifically and directly call to the database, but this requires that the database location and name be known and FIXED.
Otherwise the sql will fail if the dependencies change.

No, this is for work.  If it was for school, I'd just take a lower grade :)  Since this pays the rent, I have no choice.

We want to eventually port this over to VB, and we anticipate 100 users or more.  We feel like too many ODBC connections would bog down network resources.  I did have this project mostly completed with ODBC, and I've noticed that it comes with a substantial performance penalty.  It has been our mission to make this project as slender and portable as possible-  that's why there are so many limitations on what I can do.  I'm essentially using Access as a developement framework, for now.

(Also, we are trying to avoid using the Variant data type in case we move it over to VB.NET)

I know that there is a way to "Link" tables using OLEDB, and that would be okay.  I'm not sure that it would help in this situation, but I'm trying to avoid it.  Atleast for now.

Private Sub Form_Load()
Dim cls As dbCollection
Set cls = New dbCollection
Dim str As String
'Set rst =
strRowSource = cls.RowSourceString  '<- Call function that returns RowSource string
                                                '(kept in a class module, reference must be set)

   lst_NewCollections.RowSourceType = "Value List"
       With lst_NewCollections
            .RowSource = strRowSource
            .ColumnCOUNT = 3
            .BoundColumn = 1
        End With
       

End Sub


Public Function RowSourceString() As String

Dim rst As ADODB.Recordset
Dim IntI As Integer
Dim strIN As String
Dim strOut As String

'THere is more code here to get the recordset


On Error GoTo Clear_Null


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

RowSourceString = strOut

Debug.Print RowSourceString

Exit Function
Clear_Null:
    Err.Clear
        strIN = Chr(34) & Chr(34) & ";"
    Resume Next

End Function

Jadedata,

Thanks for your help.


I had to add this code to my playbook, but I knew I could resort to doing this.  I will probably need to use this throughout the application.  I still think there must be a way to assign each recordset field to a specific column, but I have yet to see it in any of my books.  This will work for now.

The assumption is that you must know the number of columns in the recordset before you can use this to populate your list.  I could easily make it even more flexible, but this works well enough as is.



-Greg
 

The rowsource property calls for a string value that resolves to an sql or a query/table name.  Recordset is the wrong object type.  The column bindings are set by the ordinal positions of the table/query.  You can hide 'm but you can't reorder them.  That must be done in the sql.

Right... I was using "Table/Query" as a RowSourceType in the beginning.  Then setting the list equal to the recordset.  For some reason, my list flips around the recordset's column positions.  The whole reason I asked the question was to find out if there was a way that I could explicitly set each recordset column to the list column of my choice.

Since that did not come to fruition... I decided to make a function that would make a "value list" string in the way that I want it.  That is where we are now.  

I still do not know how to use a populate the list (without a native or ODBC linked table) in the manner of my choosing- without using the function I just created.

I had a VERY similar problem and i found that setting "cnn.CursorLocation = adUseClient" made the listbox column display properly.

Here's a bigger code chunk:

    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim SQL As String
 
    cnn.Open CurrentProject.BaseConnectionString
    cnn.CursorLocation = adUseClient
           
    SQL = "Select ....
       
    rs.Open SQL, cnn, adOpenStatic, adLockReadOnly
    Set FormDisplay.lstTable.Recordset = rs

    rs.close
    cnn.close

I'm not sure whether this uses ODBC or not but see if you can set the cursor location in the 'THere is more code here to get the recordset' section of your code.

Good luck - i know how frustrating this problem is.

(the questions i opened is here https://www.experts-exchange.com/questions/20792075/Field-display-order-in-MS-Access-listbox-control.html)