Duplicate Record

[Melody]

I've posted this before, but never got any responses. Maybe someone has some suggestions:

I have a form that has four "sub" forms. (They're not technically "sub" forms, but regular forms, linked to the one main form.) They are all linked using the Customer ID (primary key in Customers table). The record source for all forms are:
Form: - Record
Source:

Site Form(main form) CustomersTable
Reimbursement Form (linked form) ReimQuery
Shipping Form (linked form) ShipQuery
Mailing Form (linked form) MailQuery
Site Admin Form (linked form) SiteAdminQuery

(All "sub" forms are accessed by command buttons on my main form.)

I have a Duplicate Record command button. When this button is clicked, I want to duplicate all info. on all forms. Ex: When I'm in Record 100 and I click the button, I want it to duplicate the Site form (and all data), but moving onto the next rec. # - 101, then duplicate all other subforms. So, duplicate all five forms with what was just entered into them. Anyone know how to begin this?

 

[Melody]

PAT - Thanks so much for your feedback, and taking the time to post all that code. This is what I've done since my post - I got on the Microsoft web site and found an article titled "How to Duplicate a Main Form and its associated subform detail records", so based on this, I put this code into the command button that duplicates the record:

Private Sub btnDuplicate_Click()
Dim dbs As DAO.Database, Rst As DAO.Recordset
Dim F As Form

'Return Database variable pointing to current database.
Set dbs = CurrentDb
Set Rst = Me.RecordsetClone

On Error GoTo Err_btnDuplicate_Click

' Tag property to be used later by the append query.
Me.Tag = Me![CustomerID]

'Add new record to end of Recordset object.
With Rst
.AddNew
![#ofElectronicClaims] = Me![#ofElectronicClaims]
![#ofClaimsLastYear] = Me![#ofClaimsLastYear]
![#ofComputers] = Me![#ofComputers]
![#ofOtherPractitioners] = Me![#ofOtherPractitioners]
![#ofProviders] = Me![#ofProviders]
!AbbreviatedSiteName = Me!AbbreviatedSiteName
!City = Me!City
!PurchasingOfficeName = Me!PurchasingOfficeName
!County = Me!County
!CustomerID = Me!CustomerID
!OrderDate = Me!OrderDate
!FederalTaxIDNumber = Me!FederalTaxIDNumber
!FullLegalNameofSite = Me!FullLegalNameofSite
![SiteAddLine#1] = Me![SiteAddLine#1]
![SiteAddLine#2] = Me![SiteAddLine#2]
![SiteAddLine#3] = Me![SiteAddLine#3]
!SiteFaxNumber = Me!SiteFaxNumber
!SitePhoneNumber = Me!SitePhoneNumber
!State = Me!State
!Zip = Me!Zip
.Update
.Move 0, .LastModified
End With
Me.Bookmark = Rst.Bookmark

' Run the DuplicateReimbursementQuery append query which selects all
' detail records that have the CustomerID stored in the form's
' Tag property and appends them back to the detail table with
' the CustomerID of the duplicated main form record.

DoCmd.SetWarnings False
DoCmd.OpenQuery "DuplicateReimbursementQuery"
DoCmd.SetWarnings True

'Requery the subform to display thenewly appended records.
Me![Reimbursement Subform].Requery

Exit_btnduplicate_Click:
Exit Sub

Err_btnDuplicate_Click:
MsgBox Error$
Resume Exit_btnduplicate_Click:
End Sub

Now I'm getting an error saying that the change I've requested to the table can't be made because it would cause dupes. Remove indexing or allow dupes. The only field that doesn't allow dupes is the CustomerID, which is the primary key in my main form (site info form) and main table Customers.

Is there any way you know of to keep the code I've used above and work around this primary key duplication problem?

Thanks!!!

 

[charityg]

on which line of code are you getting your error. You might want to make sure that your append queries are assigning the new CustomerID to the appended records rather than the original. If you have an index 'allow duplicates false' on this field then it would cause an error.

 

[Melody]

PAT & CHARITY - Thanks for responses. I've taken a different approach to this. I've eliminated the query but kept all the rest of the code, minus the CustomerID. I've been told that it's probably easier to have a Duplicate button within each form rather than asking the one duplicate button to duplicate the main form record and all linked form records. My main form is working fine. However, I've duplicated this code and placed it in a command button on one of the linked forms and I get an error saying "field cannot be updated." These sub forms have no navigational buttons because there will only be one record in the "sub" forms linked to the main record. I don't know where the problem is . . . .