Solved Get table field names but sorted

[Gasman]

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.

Type Mismatch when Trying to Fill Array from Field Names

I am trying to query a table for its field names and then put those names in an array to then fill a combobox with. I have gotten the query to work but I keep getting an error when trying to populate the array. Here is the code I created: Public Function fnGetColumnNames(table_name As...

www.accessforums.net

 

[jdraw]

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.

'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));"

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_name	field_name
Customers	Address
Customers	City
Customers	CompanyName
Customers	CompSoundex
Customers	ContactName
Customers	ContactSoundex
Customers	ContactTitle
Customers	Country
Customers	CustomerID
Customers	Fax
Customers	Phone
Customers	PostalCode
Customers	Region

 

[moke123]

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.

 

[Gasman]

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]

you can call a function to fill your listbox/combobox.
see form1 of the demo db.

 

[Gasman]

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]

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.

Thanks @moke123
I've not played with dictionaries before.
Wish I had an excuse to use one now.

Useful to know.

O/P has two methods now to get what they were after.

 

[jdraw]

Paul,
Here is Demo of another approach -- deals with ArrayList.

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

' ----------------------------------------------------------------
' 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]

Here is a very simple way to fill a combobox with the sorted field list

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]

Blimey, the O/P has a wealth of choices now.
Thank you all.

 

[MarkK]

Here's a way you can create a disconnected ADODB.Recordset, add the field names from a DAO.Recordset, and sort it.

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...

Set Me.cboMyCombo.Recordset = GetSortedFieldNameRecordset(Me.Recordset)