Solved Existing VBA sorts fields (in table) in ASC order... need to slightly modify code to include table name (1 Viewer)

[EEH_Help]

Good morning:

I need some assistance with slightly modifying a **working** VBA solution which sorts field names in various selected tables. Please see attached DB with contains the working solution.

Background:
- Attached DB includes 7 tables.
- Table "00_Excluded_Tables" includes table names that I do NOT want to show in my listbox (incl. "Excluded" itself + "_Backup" tables" + "Charlie" tables).
- Thus, focus for the VBA *sort* routine will only be 2 tables: "tbl_Alpha" and "tbl_Bravo"

Current Process:
- Open form "F01_MainMenu"
- From the listbox, select either "tbl_Alpha" OR "tbl_Bravo"
- Click on command button "Sort Fields"
- Based on the VBA, all fields in the selected table will be sorted in ASC order. However, per VBA code, fields "ID", "SOURCE_FILE", "REPORT_NUMBER" will always remain in 1st, 2nd, and 3rd order.
- To repeat the process, replace, e.g., "tbl_Alpha" with "tbl_Alpha_Backup" and then rename it back to "tbl_Alpha"

Again, the above process works great! No modification is needed when using the forms and its associated listbox to sort fields in a selected table.

Now, here's what I need some help with:
- For a different application, I only need to sort one (and only one) table.
- I want to use the same exact method (VBA) except I need to replace all "Form" and its "Listbox" references with a specified table name.
- So, instead of the code below, I want to hard-code table name "tbl_Charlie" into the VBA function.

With Forms!F01_MainMenu!lstSourceFile
        For Each v In .ItemsSelected
            sTable = .ItemData(v)
            Set tdf = db.TableDefs(sTable)
            For Each fld In tdf.Fields

- Ultimately I want to remove the listbox box and
- when I click on "Sort Fields", I automatically want to apply the function to to table "tbl_Charlie" (where again I still keep the order of the first three fields in the same order).

Something like below (except that below doesn't work yet):

Set tdf = "tbl_Charlie"
            For Each fld In tdf.Fields
          
                Select Case fld.Name
                               ...
                               ...
                               ...
                End Select
            Next fld
          
Set tdf = Nothing

My question:
- How should the VBA be modified so that I can delete the listbox from the form and replace all associated code with table name "tbl_Charlie"?

 

[EEH_Help]

Solved it....

    Set db = CurrentDb

            sTable = "tbl_Charlie"
            Set tdf = db.TableDefs(sTable)
            For Each fld In tdf.Fields
            
                Select Case fld.Name
                    Case "ID"                           'Always move [ID] into 1st position
                         fld.OrdinalPosition = 0
                    Case "SOURCE_FILE"                  'Always move [SOURCE_FILE] into 2nd position
                         fld.OrdinalPosition = 1
                    Case "REPORT_NUMBER"                'Always move [REPORT_NUMBER] into 3rd position
                         fld.OrdinalPosition = 2
                    Case Else                           'Remaining fields will be sorted in ASC order
                         fld.OrdinalPosition = 4
                End Select
            Next fld
    Set tdf = Nothing

 

[arnelgp]

see the function in Module1.