Hi
@Pat Hartman
i want to clarify something
based on the code that you gave me
Private Sub CopyRecord_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim td As DAO.TableDef
Dim SaveNewId As Long
Dim SaveOldID As Long
Dim strSQL As String
SaveOldID = Me.TransactionID - this is my primary key in main form and this is my foreign key for the subform
Set db = CurrentDb
Set td = db.TableDefs("Transactions") - this is the table which relates to my main form
Set rs = td.OpenRecordset(dbOpenDynaset, dbSeeChanges)
rs.AddNew
rs![Date Expected] = Me.[Date Expected] ----- all the fields mentioned here are from my main form
rs![PO Date] = Me.[PO Date]
rs![Requisitioner] = Me.[Requisitioner]
rs![SupplierID] = Me.[SupplierID] ----- supplier and destination ID's are my bound to dropdown fileds which updates details for supplier and destination
rs![Destination ID] = Me.[Destination ID]
SaveNewId = rs!TransactionID --- - this is my primary key in main form and this is my foreign key for the subform
rs.Update
strSQL = " Insert Into Transactions ([Date Expected],[PO Date],[Requisitioner],[SupplierID],[Destination ID])" ----- table name mentioned here is my main form table name along with the fields from that table
strSQL = strSQL & " Select [Description], " & SaveNewId & " From TransactionsDetails" --- description is a field in my subform and
transactiondetails is the table that relates to subform
strSQL = strSQL & " Where TransactionID = " & SaveOldID
db.Execute strSQL, dbFailOnError
End Sub
Please read my explanation above and correct me if i am wrong
Note: here for demo i have just added description field but i will be adding more fields to that statement
Please help and correct me
@Pat Hartman