Duplication Code for Main Form and five Sub-Form records

[cwats]

Hi,

I have a main form with five subforms on it.

I need to duplicate the main forms and subforms recordsets at the same time using one command button on the main form.



Thank you.

 

[Pat Hartman]

One command button is fine but you'll need to run 6 append queries. One for each table. Assuming that the five subforms are children of the main form, the simplest solution to get the the PK of the new mainform record is to use the .AddNew method of DAO rather than running an append query for the "parent" table. By using the .AddNew, you will be able to directly access the newly generated autonumber. When you run an append query and the BE is Jet/ACE, you have NO WAY to absolutely determine the ID of the record you just inserted when you are operating in a multi-user environment. With SQL Server, there is a way to get the last ID inserted via the current thread but not when the BE is Jet/ACE. So this is a case where I defer to the "Access" method and use .AddNew. Once you have the PK that you need to use as the FK for the 5 child tables, you can pass that to the 5 append queries.

 

[pbaldy]

Here's more on what Pat is describing:

http://allenbrowne.com/ser-57.html

 

[Pat Hartman]

wow! What happened to Allen's site. It's really hard to read.

 

[pbaldy]

It's looking normal to me, but I'm not the best judge. Does it look like this to you?

 

[Pat Hartman]

Now it does. The first time I clicked it, there was a large banner across the top, the background of the window was black and some of the text was yellow and some was blue. The blue was unreadable. There were also three or four lines between each line of code. I should have taken a screen shot.

 

[cwats]

Thank you for the example. I will try that code and see what happens.

 

[arnelgp]

if your FK key on each subform table is based on Autonumber Key of mainform, allenbrowne's code will fail.

 

[cwats]

Hey i tried the code example out and cant seem to figure it out. i got error number 3022.



Private Sub cmdfrmTopLevelDupe_Click()
'On Error GoTo Err_Handler
'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.

'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."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!PRODUCT_ID = Me.PRODUCT_ID
'etc for other fields.
.Update

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

'Duplicate the related records: append query.
If Me.sfrmQryprodcontainers.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tbl_ProductContainerslist] ( Container_ID, ProdContainersList_ID, PRODUCT_ID, Container_ID, ContainerDescription, ContCostOverride, BaseQty, Yield, YieldQty, ExtCost ) " & _
"SELECT " & lngID & " As NewID, Qryprodcontainers.tbl_Containerslist.Container_ID, Qryprodcontainers.ProdContainersList_ID, Qryprodcontainers.PRODUCT_ID, Qryprodcontainers.tbl_ProductContainerslist.Container_ID, Qryprodcontainers.ContainerDescription, Qryprodcontainers.ContCostOverride, Qryprodcontainers.BaseQty, Qryprodcontainers.Yield, Qryprodcontainers.YieldQty, Qryprodcontainers.ExtCost " & _
"FROM Qryprodcontainers WHERE Qryprodcontainers.PRODUCT_ID = " & Me.PRODUCT_ID & ";"

DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If

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

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler
End Sub

 

[arnelgp]

the error means the PK already exists?

 

[GinaWhipp]

You can't have...

!PRODUCT_ID = Me.PRODUCT_ID

That is the PK for the current record, remove that. Are there any fields from the main record you want to carry over? Those are the only ones you should be copying over in that section.

 

[pbaldy]

if your FK key on each subform table is based on Autonumber Key of mainform, allenbrowne's code will fail.

Unless I'm misunderstanding what you're saying, that's exactly what Allen's code handles. It adds a new main record, grabs the autonumber value from the new record and uses is for the FK in the subform table(s). I use the same method myself, after stealing it from him.