Duplicating sub form records

You've done much as I expected, and approached the project in a spreadsheet manner, rather than as a relational database.  In the latter each table models an entity type, and each column in the table models an attribute which is functionally determined solely by the while of the key. Each table will generally have relatively few columns, but many rows.

I suspect you'll probably feel that you are in too deep for the radical redesign which would be involved in changing the database to one which conforms to the principles of the database relational model, but essentially what you'd have would be a table of orders, a table of garment types, and a table of garment features, for want of a better word, as values in columns, not as column headings.  The order specification would be a many-to-many relationship type between these tables, which would be modelled by a further table which resolves the relationship type into three one-to-many relationship types.  Non-key columns in this table represent the attributes of the relationship type, e.g. with the example of collars which you've posted, a value such as 'semi-spread collar' would be an attribute value of the ternary relationship type between garment type shirt, feature collar and the order in question.

Moreover, the above is a simplification of the model.  It would also contain other relationship types modelled by further tables to enforce constraints between the entity types, e.g. trousers don't have collars, so a further table would model the relationship type between trousers and features and would define those features pertinent to trousers, and similarly between coats/shirts/vests, and other features.  The nearest example I can give you to this is Relationships.zip in the same OneDrive folder to which gave you a link.  This uses a simplified medical prescriptions model as its example, to illustrate how relationships are built up across a database to achieve a robust model.

Recasting the database into a correct model would be a major undertaking, and my guess is that you won't want to go down that road.  You can of course still do the same as in my CopyTree demo, but you'd have to assign values for each column in the tables.  While the code would therefore be 'bulky', writing it is a one-off exercise, and its execution should still be efficient.

A sales or purchase order is normally modelled by a many-to-many relationship type between Orders and Products tables or similar, with the relationship type being modelled by an OrderDetails table or similar.  To duplicate the order therefore requires a new row to be inserted into Orders, followed by the insertion of new rows into OrderDetails with the OrderID foreign key column now referencing the primary key of the newly inserted row in Orders.

You'll find an example of this in CopyTree.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.

This little demo file illustrates the copying of a tree structured hierarchy, and of a many-to-many relationship type.  The latter is appropriate to what you are attempting.  The code is in the following CopyMTMRel function:

Public Function CopyMTMRel(lngOrderID As Long, lngCustomerID As Long)

    Dim rst As DAO.Recordset

    Dim strSQL As String

    Dim lngNextOrderID As Long

    Dim lngItemID As Long

    Dim intQuantity As Integer

    Dim curCurrentUnitPrice As Currency

    lngNextOrderID = DMax("OrderID", "Orders") + 1

    ' insert new row into orders with today's date as order date

    strSQL = "INSERT INTO Orders(OrderID,CustomerID,OrderDate) " & _

        "VALUES(" & lngNextOrderID & "," & lngCustomerID & ",#" & _

        Format(VBA.Date, "yyyy-mm-dd") & "#)"

    CurrentDb.Execute strSQL, dbFailOnError

    'return recordset of current order's rows in OrderDetails

    ' and loop through recordset, inserting a new row

    ' at each iteration of the loop, with current unit price of item

    strSQL = "SELECT * FROM OrderDetails" & _

        " WHERE OrderID = " & lngOrderID

    Set rst = CurrentDb.OpenRecordset(strSQL)

    With rst

        If Not (.BOF And .EOF) Then

            .MoveLast

            .MoveFirst

            Do While Not .EOF

                lngItemID = .Fields("ItemID")

                intQuantity = .Fields("Quantity")

                ' get current unit price of item

                curCurrentUnitPrice = DLookup("UnitPrice", "Items", "ItemID = " & .Fields("ItemID"))

                strSQL = "INSERT INTO OrderDetails(OrderID,ItemID,UnitPrice,Quantity) " & _

                    "VALUES(" & lngNextOrderID & "," & lngItemID & "," & curCurrentUnitPrice & "," & intQuantity & ")"

                CurrentDb.Execute strSQL, dbFailOnError

                .MoveNext

            Loop

        End If

    End With

End Function

In the demo the orders form has only one subform, bound to OrderDetails.  As you have three subforms, you would need to repeat the code which follows the insertion of the new row into Orders three times, changing it each time to loop through a recordset of the relevant rows from the relevant subform's table and insert new rows into the table.

Note how, despite the fact that the primary key of Orders is an autonumber, the next number in sequence is inserted by first calling the DMax function and adding 1 to get the next number.  The same number is then inserted into OrderDetails as the foreign key value.