Copy record on form with 2 subforms to new record

[JulieK]

I am trying to copy the data on a form that has 2 subforms to a new record. My primary key is calculated based on another hidden subform. I am getting the following error:

Error 3061 - Too few paramters. Expected 2.

I'm using the following code:

Private Sub cmdcopy_Click()
On Error GoTo Err_Handler

Dim strSql As String
Dim lngID As String


If Me.Dirty Then
Me.Dirty = False
End If


If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else

With Me.RecordsetClone
.AddNew
!numsrf = [frmdcount]!txtsrf
!dtmreqdate = Date
!dtmreqtime = Now
!dtmdisdate = IIf(Format([dtmreqdate] + 2, "ddd") = "Sun", [dtmreqdate] + 3, IIf(Format([dtmreqdate] + 2, "ddd") = "Sat", [dtmreqdate] + 4, [dtmreqdate] + 2))
!dtmdistime = "15:00"
!numbottles = Me.numbottles
!txtstype = Me.txtstype
!ynfiltered = Me.ynfiltered
!ynpreship = Me.ynpreship
!txtsize = Me.txtsize
!txtreqby = Me.txtreqby
!txtcharge = Me.txtcharge
!txtptype = Me.txtptype
!numcourier = Me.numcourier
!txtocourier = Me.txtocourier
!meminstructions = Me.meminstructions
!numcustomer = Me.numcustomer
!txtlabel = Me.txtlabel
!txtsw = Me.txtsw
!txtvarietycode = Me.txtvarietycode
!txtvintage = Me.txtvintage
!numblend = Me.numblend
.Update


.Bookmark = .LastModified
lngID = [frmdcount]!txtsrf


If Me.[frmtanks_edit].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO tbltanks ( numsrf, txttank, txtbatch, numper ) " & _
"SELECT [Forms]![frmdcount]![txtsrf] AS NewID, tbltanks.txttank, tbltanks.txtbatch, tbltanks.numper " & _
"FROM tbltanks WHERE (((tbltanks.numsrf)=[Forms]![frmsrfform_edit]![numsrf]));"


DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related tanks."
End If
If Me.[frmadds_edit].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO tblsadds ( numsrf, numaddid, numrate, ynbot ) " & _
"SELECT [Forms]![frmdcount]![txtsrf] AS NewID, tblsadds.numaddid, tblsadds.numrate, tblsadds.ynbot" & _
"FROM tblsadds WHERE (((tblsadds.numsrf)=[Forms]![frmsrfform_edit]![numsrf]));"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related additives."
End If


Me.Bookmark = .LastModified
End With
End If
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdcopy_Click"
Resume Exit_Handler
End Sub

[June7]

Already marked solved. Is it? Want to share solution?

[JulieK]

Happy to share my round about way of achieving what I needed. It may not be the most efficient way but it works.

I created 2 tables (tblsrfadds & tblsrftanks) to temporarily hold data I needed to copy from the subforms and ended up with 4 queries to suffle the data around to get it to copy the correct way. Below is the resulting vba code I used:

Private Sub cmdcopy_Click()

DoCmd.RunSQL "DELETE * FROM tblsrfadds" 'Delete any data currently in the tblsrfadds table.
DoCmd.RunSQL "DELETE * FROM tblsrftanks" 'Delete any data currently in the tblsrftanks table.
DoCmd.OpenQuery "qrysrfadds" 'Copy data from the adds subform (tblsadds table as record source) for the current record into tblsrfadds.
DoCmd.OpenQuery "qrysrftanks" 'Copy data from the tanks subform (tbltanks table as record source) for the current record into tblsrftanks.

'Copy the main form data to a new record.
With Me.RecordsetClone
.AddNew
!numsrf = [frmdcount]!txtsrf 'get new primary key from the frmdcount subform.
!dtmreqdate = Date
!dtmreqtime = Now
!dtmdisdate = IIf(Format([dtmreqdate] + 2, "ddd") = "Sun", [dtmreqdate] + 3, IIf(Format([dtmreqdate] + 2, "ddd") = "Sat", [dtmreqdate] + 4, [dtmreqdate] + 2))
!dtmdistime = "15:00"
!numbottles = Me.numbottles
!txtstype = Me.txtstype
!ynfiltered = Me.ynfiltered
!ynpreship = Me.ynpreship
!txtsize = Me.txtsize
!txtreqby = Me.txtreqby
!txtcharge = Me.txtcharge
!txtptype = Me.txtptype
!numcourier = Me.numcourier
!txtocourier = Me.txtocourier
!meminstructions = Me.meminstructions
!numcustomer = Me.numcustomer
!txtlabel = Me.txtlabel
!txtsw = Me.txtsw
!txtvarietycode = Me.txtvarietycode
!txtvintage = Me.txtvintage
!numblend = Me.numblend
.Update

'keep the new record as the current record.
Me.Bookmark = .LastModified
End With

DoCmd.OpenQuery "qrysrfadds2" 'Apend data from the tblsrfadds table into tblsadds using the new primary key.
DoCmd.OpenQuery "qrysrftanks2" 'Apend data from the tblsrftanks table into tbltanks using the new primary key.
DoCmd.RunCommand acCmdRefresh

End Sub