Duplicate records in a subform

[Guest]

I have a form (formA) with a subform (formB) inside formA.
I need to duplicate the record in formA which is not a problem but i also
want to duplicate the current record in formB at the same time without having
to place another duplicate button in this form.

Can anyone help me with this?

 

[Allen Browne]

The example below duplicates the invoice record in the main form and the
line items from the subform.

It illustrates 2 techniques:
- The main record is duplciated in the form's RecordsetClone (using DAO).
This gives you to new primary key value, which you need for the related
records.

- The child records are duplicated by executing an append query statement.
This creates them all in one pass.

The code then displays the newly created duplicate.

Private Sub cmdDupe_Click()
Dim strSql As String
Dim db As DAO.Database
Dim lngInvID As Long

Set db = DBEngine(0)(0)

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else

'Duplicate the main record
With Me.RecordsetClone
.AddNew
!InvoiceDate = Date
!ClientID = Me.ClientID
'etc for other fields.
.Update
.Bookmark = .LastModified
lngInvID = !InvoiceID

'Duplicate the related records.
If Me.fInvoiceDetail.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO tInvoiceDetail ( InvoiceID, Item,
Amount ) " & _
"SELECT " & lngInvID & " As NewInvoiceID,
tInvoiceDetail.Item, " & _
"tInvoiceDetail.Amount FROM tInvoiceDetail " & _
"WHERE (tInvoiceDetail.InvoiceID = " & Me.InvoiceID &
");"
db.Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

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

Set db = Nothing
End Sub

 

[Allen Browne]

Barry, this gets asked fairly often, so have created a web page explaining
the technique, and illustrating it with code that works with Northwind.

The article is:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html

 

[Guest]

Thanks allen the code has helped me alot and with the explanation i should
now be able to get this working for me.

Thanks

 

[Guest]

I have a problem with this code

it stops at the line DBEngine(0)(0).Execute strSql, dbFailOnError and gives
an
error message "Syntax error in INSERT INTO statement"

dbFailOnError has a value of 128 if this helps

 

[Allen Browne]

The message indicates that you SQL string is not correct.

Immediately above the dbEngine... line, add this line:
Debug.Print strSQL
Run the code.
When if fails, open the Immediate Window (Ctrl+G).
Can you see what's wrong with the statement? E.g.:
- Spaces missing?
- No value where you expected one?
- Wrong field names?
- Field or table names that contain spaces, that were not in square
brackets?

Mock up a dummy query to compare it to if that helps.

 

[Guest]

i am not sure what the "NewID" is in the statement
"SELECT " & lngID & " As NewID

is this a function that is being called or should this be a field in the table

 

[Allen Browne]

NewId is just the alias for the number.
You could call it anything at all.
The statement should end up as:
SELECT 789 AS YNotThisName, ...

What matters is that the items in the SELECT clause match the same fields as
those inside the brackets in the INSERT clause.

 

[Guest]

I have got past this part of the code, I am using an SQL server to store the
data but now i receive an error " You must use dbSeeChanges option with
OpenRecordSet when accessing an SQL server table that has an identity column"

I have tried the following statement but i then get an error "Object required"
Set Duplicate = dbs.OpenRecordset(strSql, dbOpenDynaset, dbSeeChanges)

This gives duplicate a value of empty???
Can you help me...

 

[Guest]

'Duplicate the related records: append query.
If Me.[Pre_Installation_SiteSurvey
subform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [Pre_Installation_SiteSurvey] (
lngProgramID, PreInstallationID, [Scheduled Week No], [Completed Week],
[Survey By], Paperwork)" & _
"SELECT " & lngID & " As
lngProgramID,PreInstallationID,[Scheduled Week No],[Completed Week],[Survey
By], Paperwork " & _
"FROM [Pre_Installation_SiteSurvey] WHERE lngProgramID=
" & Me.txtProgramID & ";"

Debug.Print strSql
Set Duplicate = dbs.OpenRecordset(strSql, dbOpenDynaset,
dbSeeChanges)
DBEngine(0)(0).Execute strSql, dbFailOnError

Else
MsgBox "Main record duplicated, but there were no related
records."
End If

 

[Allen Browne]

I don't understand what the OpenRecordset is for. Executing the append query
statement should insert the value, without any OpenRecordset.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

'Duplicate the related records: append query.
If Me.[Pre_Installation_SiteSurvey
subform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [Pre_Installation_SiteSurvey] (
lngProgramID, PreInstallationID, [Scheduled Week No], [Completed Week],
[Survey By], Paperwork)" & _
"SELECT " & lngID & " As
lngProgramID,PreInstallationID,[Scheduled Week No],[Completed
Week],[Survey
By], Paperwork " & _
"FROM [Pre_Installation_SiteSurvey] WHERE lngProgramID=
" & Me.txtProgramID & ";"

Debug.Print strSql
Set Duplicate = dbs.OpenRecordset(strSql, dbOpenDynaset,
dbSeeChanges)
DBEngine(0)(0).Execute strSql, dbFailOnError

Else
MsgBox "Main record duplicated, but there were no related
records."
End If

I have got past this part of the code, I am using an SQL server to store
the
data but now i receive an error " You must use dbSeeChanges option with
OpenRecordSet when accessing an SQL server table that has an identity
column"

I have tried the following statement but i then get an error "Object
required"
Set Duplicate = dbs.OpenRecordset(strSql, dbOpenDynaset, dbSeeChanges)

This gives duplicate a value of empty???
Can you help me...