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.
- 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):
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"?
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.
Code:
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):
Code:
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"?
Attachments
Last edited: