Solved Get table field names but sorted (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 14:00
Joined
Sep 21, 2011
Messages
14,052
Is there a way to read fieldnames of a table in alphabetical order?
Eg for a combo, instead of field list option, which is not sorted.
Just the fact the property sheet can, would lead me to believe it is possible?

The query is in reference to this thread, which got me curious.
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:00
Joined
Jan 23, 2006
Messages
15,364
Paul,

I have an old routine to get info from TableDefs and store in a table (Data_Dictionary).
This can be accessed with a query to put fields in sorted sequence.
Let me know if you need more info.

Code:
'SQL to Create the data_dictionary table
40    strSQL_Create = "CREATE TABLE data_dictionary" & _
                    "(EntryID AUTOINCREMENT PRIMARY KEY,table_name varchar(250),table_description varchar(255)," & _
                    "field_name varchar(250),field_description varchar(255)," & _
                    "ordinal_position NUMBER, data_type varchar(18)," & _
                    "length varchar(5), default varchar(30));"


Code:
SELECT data_dictionary.table_name, data_dictionary.field_name
FROM data_dictionary
WHERE (((data_dictionary.table_name) ="Customers"))
ORDER BY data_dictionary.table_name, data_dictionary.field_name;

Query61 Query61

table_namefield_name
CustomersAddress
CustomersCity
CustomersCompanyName
CustomersCompSoundex
CustomersContactName
CustomersContactSoundex
CustomersContactTitle
CustomersCountry
CustomersCustomerID
CustomersFax
CustomersPhone
CustomersPostalCode
CustomersRegion
 
Last edited:

moke123

AWF VIP
Local time
Today, 10:00
Joined
Jan 11, 2013
Messages
3,852
In the example I posted in that thread you can sort the dictionary example.

you can also apply sorts to collections, etc. see http://www.cpearson.com/excel/CollectionsAndDictionaries.htm

heres the dictionary part:

edit: modified procedure to make it more portable.
 

Attachments

  • SortDict.zip
    68.7 KB · Views: 167
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 14:00
Joined
Sep 21, 2011
Messages
14,052
Thanks guys. I will post a link to this thread on the other site.
I was thinking on just a query though, just for what the o/p was trying to do.
At a hospital atm, will look at attachments when I get home.
Thanks again.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:00
Joined
May 7, 2009
Messages
19,175
you can call a function to fill your listbox/combobox.
see form1 of the demo db.
 

Attachments

  • ListRowSource.accdb
    512 KB · Views: 146

Gasman

Enthusiastic Amateur
Local time
Today, 14:00
Joined
Sep 21, 2011
Messages
14,052
Thank you arnel,
That was the method that the o/p was trying to use, well with an array at least, but was not defining the array correctly, as I saw it.

I'll post a link to your DB in that forum.
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:00
Joined
Sep 21, 2011
Messages
14,052

jdraw

Super Moderator
Staff member
Local time
Today, 10:00
Joined
Jan 23, 2006
Messages
15,364
Paul,
Here is Demo of another approach -- deals with ArrayList.

' NOTE: DEPENDENCY
' Net Framework 3.5 to enable System.Collections for ArrayList etc
.


Code:
' ----------------------------------------------------------------
' Procedure Name: SortFieldNamesInTable
' Purpose: To list field names in Table alphabetically (ASC/DESC)
' NOTE: DEPENDENCY
' Net Framework 3.5 to enable System.Collections for ArrayList etc.
' Procedure Kind: Sub
' Procedure Access: Public
' Parameter TableName (String): Name of Table whose fields are to be listed
' Author: Jack
' Date: 27-Apr-22
' ----------------------------------------------------------------
Sub SortFieldNamesInTable(TableName As String)
          Dim db As DAO.Database
          Dim tdf As DAO.TableDef
          Dim fld As DAO.Field
          Dim arr
          Dim i As Integer
          Dim oArrayList As Object
10        Set oArrayList = CreateObject("System.Collections.ArrayList")
20        Set db = CurrentDb
30        Set tdf = db.TableDefs(TableName)
40        For Each fld In tdf.Fields
50            oArrayList.Add fld.name
60        Next
70        oArrayList.Sort   'ascending sort the elements in the structure
      
          'Print the arrayList to immediate window
80        Debug.Print vbCrLf & "Ascending ArrayList  " & vbCrLf
          Dim vloop As Variant
90        For Each vloop In oArrayList
100           Debug.Print vloop
110       Next vloop
    
          ' Put field list into an array(if needed) and Print each cell
120       arr = oArrayList.toarray
130       Debug.Print vbCrLf & " Contents of  array Arr " & vbCrLf
140       For i = LBound(arr) To UBound(arr)
150           Debug.Print "Arr(" & i & ")   " & arr(i)
160       Next i

        'decending sort the elements in the structure
170       oArrayList.Reverse
      
          'Print the arrayList descending order to immediate window
180       Debug.Print vbCrLf & "Descending ArrayList  " & vbCrLf
190       For Each vloop In oArrayList
200           Debug.Print vloop
210       Next vloop

          'cleanup
220       Set fld = Nothing
230       Set tdf = Nothing
240       Set db = Nothing
End Sub

Sample output

SortFieldNamesInTable("Orders")

Ascending ArrayList

CustomerID
EmployeeID
Freight
OrderDate
OrderID
RequiredDate
ShipAddress
ShipCity
ShipCountry
ShipName
ShippedDate
ShipPostalCode
ShipRegion
ShipVia

Contents of array Arr

Arr(0) CustomerID
Arr(1) EmployeeID
Arr(2) Freight
Arr(3) OrderDate
Arr(4) OrderID
Arr(5) RequiredDate
Arr(6) ShipAddress
Arr(7) ShipCity
Arr(8) ShipCountry
Arr(9) ShipName
Arr(10) ShippedDate
Arr(11) ShipPostalCode
Arr(12) ShipRegion
Arr(13) ShipVia

Descending ArrayList

ShipVia
ShipRegion
ShipPostalCode
ShippedDate
ShipName
ShipCountry
ShipCity
ShipAddress
RequiredDate
OrderID
OrderDate
Freight
EmployeeID
CustomerID
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:00
Joined
May 21, 2018
Messages
8,463
Here is a very simple way to fill a combobox with the sorted field list
Code:
Public Sub SortedFieldList(cmbo As Access.ComboBox, TableName As String)
  Dim rs As dao.Recordset
  Dim fld As dao.Field
  Dim itm As String
  Dim i As Integer
  With cmbo
    .RowSourceType = "Value List"
    .RowSource = ""
    .ColumnCount = 2
    .ColumnWidths = "0; " & .Width
  End With
  Set rs = CurrentDb.OpenRecordset(TableName)
  For Each fld In rs.Fields
    itm = rs.Name & "; " & fld.Name
    If cmbo.ListCount = 0 Then
      cmbo.AddItem itm
    Else
      For i = 0 To cmbo.ListCount - 1
        If i = cmbo.ListCount - 1 Then cmbo.AddItem itm
        If cmbo.Column(1, i) > fld.Name Then
          cmbo.AddItem itm, i
          Exit For
        End If
      Next i
    End If
  Next fld
End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:00
Joined
Sep 21, 2011
Messages
14,052
Blimey, the O/P has a wealth of choices now. :)
Thank you all.
 

MarkK

bit cruncher
Local time
Today, 07:00
Joined
Mar 17, 2004
Messages
8,178
Here's a way you can create a disconnected ADODB.Recordset, add the field names from a DAO.Recordset, and sort it.
Code:
Function GetSortedFieldNameRecordset(rst As DAO.Recordset) As ADODB.Recordset
    Const FN As String = "FieldName"
    Dim rs As New ADODB.Recordset
    Dim fld As DAO.field
    
    With rs
        .Fields.Append FN, adVarChar, 255
        
        .CursorType = adOpenKeyset
        .CursorLocation = adUseClient
        .LockType = adLockPessimistic
        
        .Open

        For Each fld In rst.Fields
            .AddNew
            .Fields(0).Value = fld.Name
            .Update
        Next
        
        .sort = FN
    End With
    
    Set GetSortedFieldNameRecordset = rs
End Function
To show the result in a ComboBox, just do...
Code:
Set Me.cboMyCombo.Recordset = GetSortedFieldNameRecordset(Me.Recordset)
 

Users who are viewing this thread

Top Bottom