Insert entire row from query into new row in another table

[chris.williams]

I have a query with x number of fields. I would like to copy an entire record in the query into a new record in an unrelated table.

The field names in the query and the table match but there are more fields in the table than in the query.

How can I copy an entire row from the query into a new row in the table - without specifying each and every target field and value? I have run up against the 256 character limit of the SQL statement.

eg: Query has field 1, 3, 4, 6, 8 & 10
Table has fields 1,2,3,4,5,6,7,8,9,10

Thx...

[apr pillai]

Copy the following Code into a Standard Module and save it:

Public Function CopyRecord(ByVal intblQry As String, ByVal outTbl As String)
Dim db As Database, rst1 As Recordset, rst2 As Recordset
Dim fldLoop1 As Field, fldLoop2 As Field, fldName As String, fldValue

Set db = CurrentDb
Set rst1 = db.OpenRecordset(intblQry, dbOpenDynaset)
Set rst2 = db.OpenRecordset(outTbl, dbOpenDynaset)

Do While Not rst1.EOF
   rst2.AddNew
   For Each fldLoop1 In rst1.Fields
       fldName = fldLoop1.Name
       fldValue = fldLoop1.Value
       For Each fldLoop2 In rst2.Fields
           If fldLoop2.Name = fldName Then
               rst2.Fields(fldLoop2.Name).Value = fldValue
               Exit For
           End If
       Next
    Next
    rst2.Update
    rst1.MoveNext
Loop

rst1.Close
rst2.Close

Set rst1 = Nothing
Set rst2 = Nothing
Set db = Nothing

End Function

Run the Code with the following Syntax:

CopyRecord "QueryName", "TableName"

[jo15765]

You could also save the query. Then build an append query of the query you just saved, and use the query you initially saved as the source of your append query. That way it will append the records in the query.

[chris.williams]

Thanks - I will give these two solutions a shot.