Duplicating Record within Form and Four Subforms

[MelF]

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?

 

[MelF]

UPDATE: I've put the following 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

However, when I click the button now I get an error saying that the changes I've requested to the table can't be done because it would cause dupe entries - Remove the indexing or allow dupes.

The only field that doesn't allow dupes is CustomerID, the Primary key to the main table and form (Site Info form). Anyone know how I can fix this??

Thanks!

 

[ghubbell]

Hi MelF,
First question I have to ask you is what are you up to here. If I understand your question you want to copy an entire site/reimbursement/shipping/mailing/site Admin "record" and apply it again? Doesn't this end up giving you a duplicate record or is your intent to edit the new one "a little" once its pasted.

In any case... the basic command from a command buttons on click event off of the first form would be something to this effect:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

Forms!NameOfTheNextForm.SetFocus
Do it again...

and do it again...!

You would have to have allow duplicates true on the key fields (not a great plan) and nothing running in the Forms' on current events. Please let us know where you're going with this... Gord
ghubbell@total.net

 

[ghubbell]

Ahhh. Just caught your update... As above. Yhere is no good way unless you allow dups or allow null values (no more primary key...) and with your Rs , don't add a CustomerID and hope you remember to fill them all in. :-( Gord
ghubbell@total.net

 

[MelF]

GORD - Thanks for your response. To explain: Yes, that is my goal. This will eliminate A LOT of data entry for the employees. There will no doubt be minor changes to be made to each added record, but will still significantly reduce the typing within EACH added form. I do want each added record to have a different CustomerID (primary key). I didn't know how to begin this, so the code above is from Microsoft's web site, and goes into lots of detail, involves creating an append query, etc. I thought because it was on their web site, it was probably the most accurate, the title of the article is "How to Duplicate a Main Form and It's Subform Detail Records". Thanks again for your response!!

 

[ghubbell]

MelF, it's still kind of a dangerous proposition in any case... You might be better off to run one at a time as per MS, then do something like:

Forms!nameOfForm.setfocus
Forms!nameofForm!CustomerIDField.setfocus

For each step. (perhaps 4 buttons?)

Forcing the user to add the new Id number unless it's an Autonumber...the problem I've found is that it is all to easy for people doing the data entry to neglect the minor differences just because there's already data there...but it is your call! Gord
ghubbell@total.net

 

[MelF]

GORD - I'm a little confused. Are you saying you don't think there's any way to programatically fix this AutoNumber duplication problem??

Thanks!

 

[ghubbell]

If you are running with an autonumber then run exactly as MS describes but do not copy the CustomerID autonumber:

Scrap: !CustomerID = Me!CustomerID

You should then get exactly the results you want! Now thats taken care of the first form, but you'll have to do it again on all the others...do-able? Yes! Great idea? Up to you... Gord
ghubbell@total.net

 

[MelF]

GORD - O.K. - I'm beginning to think my way is going to be impossible. Back to your earlier post:

"MelF, it's still kind of a dangerous proposition in any case... You might be better off to run one at a time as per MS, then do something like:

Forms!nameOfForm.setfocus
Forms!nameofForm!CustomerIDField.setfocus

For each step. (perhaps 4 buttons?)"

What exactly do you mean by this, exactly where would I put this code, etc. (I'm a little slow!!!)

Much thanks again!!
-Melody

 

[ghubbell]

Good morning Mel, Your not slow, I'm just confusing you X-) (and myself too!)

If you take the MS code and use it from one command button on your first form (less the customerID), it should repopulate your mainform with a twin set of data.

Now, you could use the same idea on each subform (a command button making the twin using the appropriate fields). Doing this step by step might atleast be a little safer. What do you think?

Gord
ghubbell@total.net

 

[MelF]

GORD - AWESOME! It worked for my main form (Site Information). However, it's not working for my "sub" form. Let me explain how these forms are set up: Site Info (Primary key - CustomerID)is the first form they'll enter. This duplicates fine. There is a command button on this form that takes you to another form Reimbursement Form. These forms are linked on the CustomerID. This is where the problem is now. Once I get into the Reimbursement Form, I want it to duplicate what was on the Reimbursement Form within the last Site Info. form. (There are no navigation buttons on my Reimbursement form, because each site info. form will have only one reimbursement address.) Make sense??

Thanks!!

 

[ghubbell]

Hi Mel, good to see the progress!

...thinking...thinking...thinking a little more...Ok so the main form is open, are the other four open (or could or should be open) at the moment you decide to start to duplicate? If your Db isn't too big and if you'd like, feel free to zip it over and I'd be happy to give it a look-see (my treat!) I'm starting to fade a bit I'm afraid. Gord
ghubbell@total.net