Results 1 to 8 of 8
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    VBA to sort fields within tables in ASC order except 1st two fields

    Experts:



    I need some assistance with developing a VBA script which allows me to sort n fields within a **specified** table (or number of tables).

    Current process (let's say where I have 50 fields in a table):
    ================================================== ============
    1. I open a table and copy the 1st record
    2. I open MS-Excel and copy clipboard value into new workbook cell A1
    3. I apply "Text Delimiter" (comma)
    4. I copy Excel row #1 and paste (transpose) in a column
    5. I then sort that data range (in column)
    6. I then add commas into column B and concatenate values in A & B into column C
    7. I copy values in column C
    8. I switch back to Access and create a SELECT * query.
    9. I replace wild card with the copied values from Excel and switch mode to a "Make Table" (e.g., [Table_New]) query and execute it.
    10. Finally, I delete [tbl_Alpha] and rename [Table_New] with [tbl_Alpha]

    While the above 10 steps work to sort (and store) the fields in the table in ASC order, it is a cumbersome process.

    Now, here's what I need some help with:
    =======================================
    - Create a VBA function in Access.
    - Upon opening form "frmSortFields" (and clicking command button), I like to see, e.g., a listbox pop up where the listbox shows all existing tables in the database.
    - I then pick, e.g., [tbl_Alpha] and/or [tbl_Bravo] from the (multi-select) listbox.
    - I then get some form of prompt asking me "Are you sure you want to sort fields in the selected tables?"
    - I click OK.
    - The VBA then deletes **original** tables and stores the sorted tables/fields with their original table names.

    Please note the following:
    ==========================
    a. I do NOT want to sort all tables. For example, I do NOT want to sort the fields in [00_tbl_Main].
    b. Thus, I want the option to choose and pick (from listbox) which tables needs to be sorted.
    c. Also, I want to be able to define my starting point for the field sort. That is, I want my current first two (2) fields [ID], [Report_Number] to remain in the 1st & 2nd position within the 2 tables.
    d. Thus, per attached snapshot, the order of fields is displayed *before* (red) and *after* (green).
    e. Allow me to re-iterate that I need to able to specify the tables (via listbox or other means). That is, I don't want to go through a For Loop which may re-shuffle all tables having, e.g., a "tbl_" prefix.
    f. Per e., I may have other tables which also have a "tbl_"prefix but their fields are NOT supposed to be sorted in ASC order.


    RECAP/QUESTION:
    ===============
    What VBA would allow me to sort n tables (either specified by me OR maybe defined in another table) which would sort all fields in ASC order EXCEPT the first, e.g., two (2) fields?

    Thank you,
    Tom
    Attached Thumbnails Attached Thumbnails Tables_Fields.JPG  
    Attached Files Attached Files

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The big question is why bother?
    - Users should not be interacting with tables; only forms and reports.
    - sorting should be done in queries
    - why would you delete and recreate a table just to apply some kind of sort order to it?

    What you want is doable to a point but makes no sense to me.
    You can write the table names to a table and sort a listbox on that quite easily. To choose fields from multiple tables is one thing but I don't know (yet) how you'd present the fields for selection unless you chose only one table at a time or maybe presented all of this in a continuous form listing each table (repeated for each field) and its fields. Easy enough to iterate over either tables, or fields in one table but I think to do multiple tables with multiple fields in one operation would require such a form - with checkboxes to pick those fields.

    EDIT - I reviewed and see that I mistakenly thought you wanted to sort data in fields, but you want to sort the order of appearance of table fields from left to right. So the correct answer has already been given IMO. Forget it and create queries with the field order you want and leave the tables alone.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Micron - there *is* a purpose for this request.

    As I pointed out, I already do what you suggested. I create a query and then put the fields in the desired order. The only difference is that I make that field order change permanent via using a make-table query. Again, there is a purpose for this (which goes beyond what I summarized here).

    Creating such query (whether in make table or SELECT mode) requires either a manual dropping of the fields into the query... Given that some tables have 50+ fields (combined with special characters) makes the ordering of fields in ASC order a bit time-consuming.

    Again, I did NOT see the need to include a full justification for my data analysis needs which *require* n fields to be in ASC order.

    So if you know of a script which does what I described in the original post, I would certainly welcome your sample code.

    Best,
    Tom
    Last edited by skydivetom; 06-30-2021 at 05:03 PM. Reason: Misspelling

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sorry, I didn't even pay attention to the author (OP) otherwise I would have known better than to make those suggestions. I don't know of such a script nor do I have any sample code. I imagine it as an outer loop (of chosen tables) and an inner loop that for each table, begins at Fields(i) as specified, then builds an array of field names, then sorts the array alphabetically. I've never done this type of thing but have used code that loops over tables and field and seen separate code that sorts an array. I suppose you'd use that array to build a make table sql that will create a table def.
    Last edited by Micron; 06-30-2021 at 05:29 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Copy all - thanks, Micron.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Based on MajP's code in the above link please try this:
    Code:
    Private Sub cmdSortSelected_Click()
    Dim tdf As DAO.TableDef, fld As DAO.Field, sTable As String, db As DAO.Database
    
    
    Set db = CurrentDb
     With Forms!F01_MainMenu!lstSourceFile
        For Each v In .ItemsSelected
            sTable = .ItemData(v)
            Set tdf = db.TableDefs(sTable)
            For Each fld In tdf.Fields
                Select Case fld.Name
                Case "ID"
                    fld.OrdinalPosition = 0
                Case "SOURCE_FILE"
                    fld.OrdinalPosition = 1
                Case "Report_Number"
                    fld.OrdinalPosition = 2
                Case Else
                    fld.OrdinalPosition = 3
                End Select
            Next fld
        Next
    End With
    Set tdf = Nothing
    End Sub
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad -- your solution does **EXACTLY** what I was hoping to achieve. Not only does this solution maintain data integrity it also allows to better organize a table. For instance, if a table has 50+ fields, finding any field may require to scroll back and forth. This nifty tool, however, allows the administrator to sort the fields in ASC order while preserving the requirement to potentially have a number of "admin" fields (regardless of ASC order) at the beginning of the table.

    Totally awesome! Thank you Vlad for sharing this MajP's code... I appreciate your reading the question and always *delivering* on the specific requirement(s).

    Cheers,
    Tom

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 08-27-2019, 08:41 AM
  2. Replies: 1
    Last Post: 04-12-2017, 07:39 AM
  3. Replies: 3
    Last Post: 01-08-2013, 02:41 PM
  4. Replies: 2
    Last Post: 10-07-2011, 08:13 PM
  5. Paradox Sort order for linking tables to access
    By Jimmy in forum Import/Export Data
    Replies: 0
    Last Post: 02-02-2010, 03:13 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums