duplicating record on main form and subforms

[Guest]

I have 4 tables. The first table has an autonumber as an ID and 3 other
field names as the primary keys. The second, third, and fourth table is
linked to the first table with an ID field (number - linked to the
autonumber) and the 3 field names in the first table, with each table also
having another primary key that is unique to that table.

On a form, I have the first table as the main form (single form), and the
three other tables as subforms (continuous forms) in the main form. The
subforms can have many records. The subforms are linked to the main form by
the ID (number) to autonumber(main form) and the 3 other field names.

I want to allow the user to be able to create any duplicate record, since
most of the data will be the same but will be able to change some data. I am
able to duplicate the record on the main form, but was not successful with
the subforms.

When I create a duplicate record, the main form copied everything over, with
the ID (autonumber) being different, but the subforms are blank.

Can anyone help me on how to create duplicates in the subforms.

Thanks.
Ashley

 

[Allen Browne]

You need to get the primary key value of the new record in the main form, so
you can use it for the related records. The simplest way to do that is to
AddNew to the RecordsetClone of the subform. Then use the new ID value in an
Append query statement to add the related records to the subform.

The example below shows how to duplicate an invoice in the main form, and
the invoice detail items in the subform.

----------------code starts-----------------------
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
'Now get the ID value of the new record.
.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

'Finally, display the duplicate.
Me.Bookmark = .LastModified
End With
End If
End Sub
----------------code ends-----------------------

 

[Guest]

I have tried the code you provided, but it now gives me the error "too few
parameters. Expected 1". I checked everything and it looks fine. I do not
know what is wrong. It will duplicate the items on the main form, but
nothing on the subform. Below is what I have in the code section.

msg = "Do you want to duplicate this record?"
style = vbYesNo
response = MsgBox(msg, style)
If response = vbYes Then
With Me.RecordsetClone
.AddNew
!J_NUMBER = Me.J_NUMBER
!MAIN_PART_NUMBER = Me.MAIN_PART_NUMBER
!REVISION = Me.REVISION
!TITLE = Me.TITLE
!CONTRACT_NUMBER = Me.CONTRACT_NUMBER
!MAIN_CAGE = Me.MAIN_CAGE
!NEXT_HIGHER_ASSEMBLY = Me.NEXT_HIGHER_ASSEMBLY
!RE = Me.RE
!ORGANIZATION = Me.ORGANIZATION
!WORK_RELEASE = Me.WORK_RELEASE
.Update
.Bookmark = .LastModified
lngMainPartNumberID = !ID

If Me.PARTS_LIST_subform.Form.RecordsetClone.RecordCount > 0 Then
strSQL = "INSERT INTO Parts_List (ID, J_Number,
Main_Part_Number, Revision, Part_Number, " & _
"Part_Type, Quantity, Shapes, Materials,
Materials_Sub, Nomenclature, " & _
"Material_Description, Material_Spec, DIA_THK,
Length, Width1, Notes, Cage, " & _
"Zone, Alt_Part_Number, Alt_Notes, Parts_List_ID)"
& _
"SELECT " & lngMainPartNumberID & " as ID,
Parts_list.J_Number, " & _
"Parts_List.Main_Part_Number, Parts_List.Revision,
Parts_List.Part_Number, " & _
"Parts_list.Part_Type, Parts_list.Quantity,
Parts_List.Shapes, Parts_List.Materials, " & _
"Parts_List.Materials_Sub, Parts_List.Nomenclature,
" & _
"Parts_list.Material_Descripton,
Parts_list.Material_Spec, Parts_List.DIA_THK, " & _
"Parts_List.Length, Parts_List.Width1,
Parts_List.Notes, Parts_List.Cage, " & _
"Parts_List.Zone, Parts_List.Alt_Part_Number,
Parts_List.Alt_Notes, " & _
"Parts_List.Parts_List_ID from Parts_List " & _
"WHERE (Parts_List.ID = " & Me.ID & ");"

db.Execute strSQL, dbFailOnError
Else
MsgBox "Main Record Duplicated, but there where no related
records."
End If

Me.Bookmark = .LastModified
End With
End If


Thanks.
ash

 

[Allen Browne]

Okay, so there is a small error in the SQL statement. There is a name that
Access can't match to a field, and so it treats it as a parameter.

To solve this, add the line:
Debug.Print strSQL
When the code runs, it will print the faulty SQL statement into the
Immediate Window.
Then press Ctrl+G, and copy the statement to clipboard.
Create a new (blank) query.
Switch to SQL View (View menu).
Paste in the statement.

That should help you pin down the name that is spelt wrong, the missing
space between words, or the square brackets needs around a field/table that
has a space in its name.