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