SubForm dataset append to table

[bdotr22]

I have a form and subform set up and it displays the data I want. the subform uses a query "qryItemList subform" based on the main form values. This part works fine.



Once I get to this stage I want to append all records in "qryItemList subform" to a table "tblProdLines" which has all of the same columns. I tried a very basic append query which is triggered by the 'Add RP Lines' button, but it seems there is more to it as this appends 0 records.



Obviously I am missing something here. Not sure if this is something that can be done just in basic access or needs Visual Basic. Any guidance would be greatly appreciated.

[June7]

Conventional syntax is:

INSERT INTO tablename(field1, field2) VALUES(something1, something2)

or

INSERT INTO tablename(field1, field2) SELECT field1, field2 FROM tableORquery

Does your table have a primary key field? Syntax you tried (using wildcards) won't work.

[bdotr22]

It just has a default primary ID, just a basic number unique identifier.

[jojowhite]

you can also use Code and add it to the click event your "Add RP Lines" button to add the records:

Private Sub cmdAddRPLines_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fd As DAO.Field
    Dim i As Integer
    Set db = CurrentDb
    ' remove this line if you need
    If MsgBox("Delete previous records from tblProdLines before appending records?", vbQuestion + vbYesNo) = vbYes Then
        db.Execute "delete * from tblProdLines;"
    End If
    Set rs = db.OpenRecordset("tblProdLines", dbOpenDynaset)
    With Me.qryItemList_subform.Form.RecordsetClone
        If .RecordCount < 1 Then
            Exit Sub
        End If
        .MoveFirst
        Do Until .EOF
            i = i + 1
            rs.AddNew
            For Each fd In .Fields
                rs.Fields(fd.Name) = fd.Value
            Next
            rs.Update
            .MoveNext
        Loop
    End With
    rs.Close: Set rs = Nothing
    Set db = Nothing
    MsgBox i & " record(s) added to tblProdLines."
End Sub

[June7]

It just has a default primary ID, just a basic number unique identifier.

I should qualify my earlier comment. Your syntax won't work because of primary key - if primary key and corresponding field are included. Changing ID field to not be primary key would result in duplicate ID values (even if it is autonumber type).

Actually, there is a way to make it work.
First, your query cannot have field corresponding to table primary key field included.
Next, primary key field in tblProdLines must be last field in table designer.
All fields in both data sources must be in the same order.

Then syntax would be:
INSERT INTO tblProdLines SELECT * FROM qryItemList

[bdotr22]

I should qualify my earlier comment. Your syntax won't work because of primary key - if primary key and corresponding field are included. Changing ID field to not be primary key would result in duplicate ID values (even if it is autonumber type).

Actually, there is a way to make it work.
First, your query cannot have field corresponding to table primary key field included.
Next, primary key field in tblProdLines must be last field in table designer.
All fields in both data sources must be in the same order.

Then syntax would be:
INSERT INTO tblProdLines SELECT * FROM qryItemList

From initial testing that seems to have worked. Thanks