I have a form that contains a subform based on query. There is acommand button that I want to copy the selected record. It is an accounts payable form and if I were to be able to copy selected records from my first form (VI
nvoices) with sub (VInvoiceQrySub), it would eliminate a lot of time and repetative data entry. Here is the latest version of the code: (Allen Browne) Modified to fit my particular needs.

Private Sub Command98_Click()
On Error GoTo Err_Command98_Click

DoCmd.OpenForm "Invoices", , , "[InvoiceID]=" & Me.VInvQry_subform.Form.[InvoiceID]
'Purpose: Duplicate the main form record and related records in the subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.
Dim frm As Form
Set frm = Forms("Invoices")
Dim sfrm As SubForm
Set sfrm = Forms!Invoices!("Invoice Details subform")



'Make sure there is a record to duplicate.
If frm.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With frm.RecordsetClone
.AddNew
!InvoiceNo = 0
!VendorID = frm.Combo22
!EmployeeID = frm.Combo33

.Update

'Save the primary key value, to use as the foreign key for the related records.
frm.Bookmark = .LastModified
lngID = !InvoiceID


'Duplicate the related records: append query.
If frm.[Invoice Details subform].Form.RecordCount > 0 Then
strSql = "INSERT INTO [Invoice Details] ( InvoiceID, [Amount], [GL Code], [GlSub], [GlSub1], [GlSub2], [GLDes] ) " & _
"SELECT " & lngID & " As NewID, Amount, GL_Code, GlSub, GlSub1, GlSub2, GLDes " & _
"FROM [Invoice Details] WHERE InvoiceID = " & .InvoiceID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If

'Display the new duplicate.
.Bookmark = .LastModified
End With
End If

Exit_Command98_Click:
Exit Sub

Err_Command98_Click:
MsgBox Err.Description
Resume Exit_Command98_Click

End Sub

Thanks in advance for any help!

Hi,



What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

Not sure if you had any question in your post. Is the code not working for you? If not, are you getting any error? Don't think we can help you without knowing what is happening.

Just my 2 cents...

It is in Access 2000 format. Basically It copies the main record just fine but does not copy any of the related records in the subform. I have tried mny different iterations and always receive error messages. I don't think that it is recognizing my subform control. With each new iteration I get error messages "Does not support this method" or "Object not defined" or one of many others. I have also tried Dim Subforms As Control as well as form. I believe the trouble lies in this line of code:

If frm.[Invoice Details subform].Form.RecordCount > 0 Then

But I have also tried:

If sfrm.[Invoice Details subform].Form.RecordCount > 0 Then
&

If sfrm.RecordCount > 0 Then

Thanks Again!

Hi,

See if this page will help you with that:

Forms: Refer to Form and Subform properties and controls

Just my 2 cents...

Thanks, I used This line of code to get through that error

If frm.Form.RecordsetClone.RecordCount > 0 Then

But, I still get this error message: Error 3061 "Too few parameters. Epected 1". I have seven selects and seven froms. Any idea why I am getting this message? I know what I am looking to do is possible, But I think I'm in over my head on this one.

Code associated:

If frm.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [Invoice Details] ( InvoiceID, [Amount], [GL Code], [GlSub], [GlSub1], [GlSub2], [GLDes] ) " & _
"SELECT " & lngID & " As NewID, Amount, GL_Code, GlSub, GlSub1, GlSub2, GLDes " & _
"FROM [Invoice Details] WHERE InvoiceID = " & .InvoiceID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If

Thanks,
Robert

Hi Robert,

Is [Invoice Details] a parameter query? If so, that would be the cause of the error.

Just my 2 cents...

Yes it is.....
Thanks For your help!

Okay, like I said, that would cause your issue. Is there any way you can avoid using a parameter query? If not, the best way, I think, to get rid of the error is to use the actual SQL of the parameter query in your code where you can resolve the parameter before executing the query.

Just my 2 cents...

Thanks DBGuy! I think you led to the cause.
Robert

Hi Robert,



Glad we could help. Good luck with your project.