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?

 

[Pat Hartman]

Your button will need to run 5 append queries. One for each table. The queries will need two parameters - OldSite and NewSite. The OldSite will be used in the where clause and the NewSite will be used as the new value for the SiteID in the appended records. The following is code from one of my databases that does this. In my case the old and new values come from fields on a form I opened to prompt for them. You may want a different method to obtain the two values. I will also post the SQL for one of the queries so you can see where the parameters go:

Public Sub CopyShipTo()
Dim OEDB As Database
Dim QD1 As QueryDef
Dim F As Form

DoCmd.RunMacro "mWarningsOff"

Set F = Forms!frmCopyShipTo
Set OEDB = CodeDb
Set QD1 = OEDB.QueryDefs!qCopyShipToOnly
QD1.Parameters![NewShipTo] = F!txtNewShipTo
QD1.Parameters![OldShipTo] = F!txtOldShipTo
QD1.Parameters![OldContractNum] = F!txtOldContractNum
QD1.Execute
QD1.Close

Set QD1 = OEDB.QueryDefs!qCopyShipToOnlyDetail
QD1.Parameters![NewShipTo] = F!txtNewShipTo
QD1.Parameters![OldShipTo] = F!txtOldShipTo
QD1.Parameters![OldContractNum] = F!txtOldContractNum
QD1.Execute
QD1.Close

Set QD1 = OEDB.QueryDefs!qCopyShipToOnlyContact
QD1.Parameters![NewShipTo] = F!txtNewShipTo
QD1.Parameters![OldShipTo] = F!txtOldShipTo
QD1.Parameters![OldContractNum] = F!txtOldContractNum
QD1.Execute
QD1.Close

DoCmd.RunMacro "mWarningsOn"
End Sub

qCopyShipToOnly =

INSERT INTO DBO_ORDER_SHIP_TO ( CONTRACT_NUM, SHIP_TO_CUST_LOC, CUSTOMER_NUM, MONTHLY_CONTRACT_AMT, MONTHLY_POSTAGE_HANDLING, FREIGHT, ITEM_TOTAL, ITEM_TOTAL_C, SHIPTO_CONTRACT_AMT, SHIPTO_COMMISSION_PCT, CONTRACT_SFX, REQ_SHIP_DATE, SHIP_METHOD, FREIGHT_METHOD, LAST_UPDT_DT, LAST_UPDT_USERID, BIC_CONTRACT )
SELECT Q.CONTRACT_NUM, Q.SHIP_TO_CUST_LOC, Q.CUSTOMER_NUM, Q.MONTHLY_CONTRACT_AMT, Q.MONTHLY_POSTAGE_HANDLING, Q.FREIGHT, Q.ITEM_TOTAL, Q.ITEM_TOTAL_C, Q.SHIPTO_CONTRACT_AMT, Q.SHIPTO_COMMISSION_PCT, Q.CONTRACT_SFX, Q.REQ_SHIP_DATE, Q.SHIP_METHOD, Q.FREIGHT_METHOD, Q.LAST_UPDT_DT, Q.LAST_UPDT_USERID, Q.BIC_CONTRACT
FROM qCopyShipToOnlySelect AS Q;

qCopyShipToOnlySelect =

SELECT S.CONTRACT_NUM, UCase([NewShipTo]) AS SHIP_TO_CUST_LOC, S.CUSTOMER_NUM, S.MONTHLY_CONTRACT_AMT, S.MONTHLY_POSTAGE_HANDLING, S.FREIGHT, S.ITEM_TOTAL, S.ITEM_TOTAL_C, S.SHIPTO_CONTRACT_AMT, S.SHIPTO_COMMISSION_PCT, S.CONTRACT_SFX, S.REQ_SHIP_DATE, S.SHIP_METHOD, S.FREIGHT_METHOD, S.LAST_UPDT_DT, S.LAST_UPDT_USERID, .[CONTRACT_NUM] & "-" & UCase([NewShipTo]) AS BIC_CONTRACT
FROM DBO_ORDER_SHIP_TO AS S
WHERE (((S.CONTRACT_NUM)=[OldContractNum]) AND ((S.SHIP_TO_CUST_LOC)=[OldShipTo]));

 

[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.

 

[Pat Hartman]

It looks like you are using the original customerID instead of a new one:

!CustomerID = Me!CustomerID

If CustomerID is an autonum, omit the line entirely. You will however need to retrieve the newly assigned number to use in the other query.

 

[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 . . . .