Dear
@Pat Hartman
I read your code many times to understand the concept
i wrote my code following your steps as i understood
but when i click on command button nothing happen
as below the code i wrote
please guide me where i do mistakes
On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the subform.
Dim str2Sql As String
Dim strSQL As String 'SQL statement.
Dim NewQuoteID As Long 'Primary key value of the new record.
Dim OldQuoteID As Long
Dim NewQuoteDetailID As Long
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim FromQD As DAO.QueryDef
Dim FromRS As DAO.Recordset
Dim ToTD As DAO.TableDef
Dim ToRS As DAO.Recordset
If SecurityAdd = True Then
Else
Exit Sub
End If
'Save any edits first
If Me.Dirty Then
Me.Dirty = False
End If
'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Exit Sub
End If
'Duplicate the main record: add to form's clone.
OldQuoteID = Me.TRPK
With Me.RecordsetClone
.AddNew
!TRPK = Me.TRPK
!TrialDate = Me.TrialDate
!TrialBy = Me.TrialBy
!QC = Me.QC
.Update
'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
NewQuoteID = !TRPK
'Display the new duplicate.
Me.Bookmark = .LastModified
End With
'Duplicate the related Item records using DAO recordset. Append Accessories with append query inside loop
Set db = CurrentDb()
Set ToTD = db!TRD_RDLog
Set ToRS = ToTD.OpenRecordset
Set FromQD = db.QueryDefs!QRD_TrialLog
FromQD.Parameters!EnterOldQuoteID = OldQuoteID
Set FromRS = FromQD.OpenRecordset(dbOpenDynaset, dbSeeChanges)
With FromRS
.MoveFirst
Do Until .EOF = True
ToRS.AddNew
ToRS!TRPK = !TRPK
ToRS!PHIPPK = !PHIPPK
ToRS!RPFPType = !RPFPType
ToRS!ERP_Code = !ERP_Code
ToRS!Item_Eng = !Item_Eng
ToRS!Brand = !Brand
ToRS!Item_Arb = !Item_Arb
ToRS!RDCode = !RDCode
ToRS!Unit = !Unit
ToRS!Unit_Arb = !Unit_Arb
ToRS!TrialPurpose = !TrialPurpose
ToRS!Kitchen = !Kitchen
ToRS!PHIPNetWt = !PHIPNetWt
ToRS!Shelflife = !Shelflife
ToRS!Storageconditions = !Storageconditions
ToRS!SampleApproval = !SampleApproval
ToRS!SampleApprovalDate = !SampleApprovalDate
ToRS!Notes = !Notes
ToRS!RecipeDate = !RecipeDate
ToRS!QuoteID = NewQuoteID
NewQuoteDetailID = ToRS!QuoteDetailID
ToRS.Update
'' copy accessories
Set qd = db.QueryDefs!qCopyAppendQuoteAcc
qd.Parameters!EnterOldQuoteDetailID = !QuoteDetailID
qd.Parameters!EnterNewQuoteDetailID = NewQuoteDetailID
qd.Execute dbSeeChanges
strSQL = "INSERT INTO TFP_PHIPDTL ( TDPK, RawCode, Unit, PQty)" & _
"SELECT [EnterNewQuoteDetailID] AS Expr1, TFP_PHIPDTL.RawCode, TFP_PHIPDTL.Unit, TFP_PHIPDTL.PQty" & _
"FROM tblQuoteAcc WHERE (((tblQuoteAcc.QuoteDetailID)=[TDPK]))"
.MoveNext
Loop
End With
Me.FFP_PHIPLog.Requery
ToRS.Close
FromRS.Close
Set ToRS = Nothing
Set FromRS = Nothing
Set db = Nothing
Exit_Handler:
Exit Sub
Err_Handler:
Select Case Err.Number
Case 3021, 2501 ' update cancelled
Resume Exit_Handler
Case Else
MsgBox Err.Number & "--" & Err.Description
Resume Exit_Handler
End Select
End Sub
thanks in advnce