Since the 2 tables have the same structure, can you create
some SQL as a base. Then determine which records you want to copy to the second table. Then, use a runsql command based on the SQL and criteria.
Something along the following lines, would seem to reduce the amount of individual field coding.
Dim baseSql as string
Dim strCrit as string
baseSql = " INSERT INTO Table2 " _
& " SELECT Table1.* " _
& " FROM Table1 WHERE "
strCrit = " [fieldX] like '*myCriteria1*' " _
& " AND [fieldY] = '" & criteria2 & "'"
DoCmd.RunSql baseSQL & strCrit