Delete "All Records* routine (via VBA)

[skydivetom]

Hello Experts:

In several previous posts, I received superb support from numerous experts (orange, moke123, accesstos, etc.). Ultimately, it allowed me to come up w/ a process which does the following (see attached sample DB):
a. Scan through one for more source tables (e.g., [OrgA] and combine data from the same fields into a single lookup (LK) table with DISTINCT values.
b. Scan through the existing table definitions and DROP all **lookup** (LK) tables where a boolean value is set to FALSE in one designated table.
c. Insert a number of pre-defined fields into all remaining LK tables.

Above steps a:c works perfectly and do NOT require any modification *whatsoever*. To follow that method, open up the attached DB and select the following steps in the following order:

-------------------------------------------------------------

From Listbox "FreeForm Tables (3 in 1)":
1. "Generate All FF Tables" ... ** please note that in this version, I have replaced the lookup (LK) concept with freeform (FF) **

You will get the following 3 messages boxes:
- "29 fields have been documented in the Data Dictionary."
- "8 FF tables were deleted from the database."
- "8 FF tables were created & loaded..."



This works great and *no changes* are needed!!

-------------------------------------------------------------

From Listbox "Sub-Routines":
2. "Delete FF Tables (non-FreeForm)"

You will get the following 3 messages boxes:
- "Do you want to delete 3 FF tables...?" --> click "Ok"
- "3 FF tables have been deleted!"

This works great and *no changes* are needed!!

-------------------------------------------------------------

From Listbox "Sub-Routines":
3. "Add Target Fields to FF Tables"

You will get the following 3 messages boxes:
- "The specified target fields have been added to 5 FF tables."

This works great and *no changes* are needed!!

-------------------------------------------------------------

Ok, here's now where I need some help. Allow me to provide some background first:
- Once the menu items "Add Target Fields to FF Tables" was selected (assuming you did it in the order of 1, 2, 3), I am left with the following 5 "FF" tables (with each still containing records)
FF_Age ... with 13 records
FF_Education ... with 6 records
FF_FirstName ... with 27 records
FF_Gender ... with 2 records
FF_LastName ... with 26 records

In contrast to "LKs" (original process), I want the ability to delete *only the records* without dropping those 5 tables. Now, the existing function "03 DeleteTablesNonFreeForm" drops all tables where
[delete_tables_nonFreeForm].[FreeForm] = FALSE.

Now, I made the following changes:
1. Copied module "03 DeleteTablesNonFreeForm" and named it "05 DeleteRecordsFreeForm"
2. Replaced VBA line **CurrentDb.Execute "DROP TABLE " & strDelete** with **CurrentDb.Execute "DELETE * FROM " & strDelete**
3. Added 3rd menu item "Delete All Records" into 2nd listbox "Sub-Routines"

Current issue:
- After selecting (one by one) menu items "Generate All FF Tables", "Delete FF Tables (non-FreeForm)", and "Add Target Fields to FF Tables", I then click menu item "Delete All Records".
- However, doing so results in an error message indicating "there are no selected tables to drop."
- Please keep in mind the latter message box is a custom message if I were to drop tables again which previously were deleted. So, it's fine for the LK concept but I don't want to see when I'm attempting to delete the
records from the remaining FF tables.

All this said, here's my question: How can I get the menu item "Delete All Records" to delete all records (when chosen) for the remaining 5 FF tables (without changing the existing modules 01_ through 04_)? In order words, how do I modify the VBA in module "05 DeleteRecordsFreeForm" to accomplish the DELETE * records for all tables with an FF_ prefix?

Thank you,
Tom

[skydivetom]

Solved issue using the following code:

Public Sub DeleteRecordsFreeForm()

    Dim T As TableDef
    
    DoCmd.SetWarnings False
    For Each T In CurrentDb.TableDefs
        If T.Name Like "FF_*" Then
            DoCmd.RunSQL "DELETE * FROM " & T.Name
        End If
    Next T
    DoCmd.SetWarnings True


End Sub