Duplicate Record 1

[supervi]

Let me start of with what we did. We made a form with 2 primary keys:

1. Ticket Number
2. Version Number

When the user clicks the duplicate record button on the form, the Ticket Number stays the same but the Version Number" primary key increments by 1 (which is what we want). The Version Number is currently AUTONUMBER. At this point everything seems good however their are a few problems

1. After the user clicks the duplicate button and clicks anywhere on the screen, it automatically jumps back to the previous records. Example... user is on record 10 ,clicks duplicate record and it creates record 11, once the user clicks in any field it moves back to record 10 instead of 11

2. On our form, we also have a subform. The subform has the master/child links to the primary keys of the main tables. Whenever the user clicks duplicate record, the subform of the new record does not populate with the information of the previous record. We figured that it is not populating because the version number is incrementing. And because of this, the subform is blank. However, we want to know if there is a way for the new record created to have the same information populated in the subform as the previous version one so that they may edit it.

Any help would be appreciated. Thanks

 

[supervi]

Just to let everyone know i kinda figured out a ghetto solution to my first problem

DoCmd.GoToRecord , , acLast

Automatically went to the new amendment

But can someone tell me hwo to populate the subform??

Refer to problem 2 of the above post for specifics

Thanks in advance

 

[Edski]

supervi,
You have a user-created button on the main form that adds a new record to the underlying table (or just the controls in the form?) and copies all the fields EXCEPT the Version Number, which is automatically incremented beacuse it is an AutoNumber. It then moves to this new record. Correct?

So you must have some code that copies the fields, one by one, to the new record in the main form, right? So why don't you just use similar code to copy the subform.

Make sure you copy the new Version Number and not the old one, especially if that's the field that is used for the join. Also, if there are multiple records in the subform, you'll have copy them as well.

All the best!
(btw: why do you need Ticket Number in the PK?)

 

[supervi]

Thanks for the response Edski. The reason we need Deal Ticket as PK is because each deal ticket has many versions...We want an audit trail for every change that happens to each ticket. Therefore, we track each change by using 2 primary keys, one for the deal ticket number itself and one called version that basically contains the exact same ticket plus the change that has occured



The button that the user clicks has the following code in it. I think this is the code that copies each value in the fields. Only problem is that it skips the subform and I dont want it to.


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70

DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append




How would I manipulate that code so that it copies the records in the subform? I was thinking along the lines of something like this


(This part to copy the form)
----- CODE START

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70

DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append



--------This part to copy each record that is in the subform

DoCmd.DoMenuItem A_FORMBAR, A_EDITMENU, A_SELECTALLRECORDS_V2, , acMenuVer20
DoCmd.DoMenuItem A_FORMBAR, A_EDITMENU, A_COPY, , acMenuVer20
DoCmd.DoMenuItem A_FORMBAR, A_EDITMENU, 6, , acMenuVer20


----------
CODE END

How do i force the DoCmd option to grab the records from the subform and not the regular form? Then how do I specifically tell it to place the copied records into the subform of the new form

 

[supervi]

I am pretty sure I can make it work if I knew how to manipulate this piece of code


DoCmd.DoMenuItem A_FORMBAR, A_EDITMENU, A_SELECTALLRECORDS_V2, , acMenuVer20

DoCmd.DoMenuItem A_FORMBAR, A_EDITMENU, A_COPY, , acMenuVer20

DoCmd.DoMenuItem A_FORMBAR, A_EDITMENU, 6, , acMenuVer20



I can't seem to get it to take all the records out from the subform. Instead it takes the record from the main form. Also i can't seem to paste the records in the subform of the new form

Any help would be greatly appreciated

Even if someone could break down the code into its respective parts and explain what each part means, then maybe from their I could derive the answer

Thanks

 

[Edski]

Hi supervi,
I'll have a look at it tomorrow if that's ok. Bit busy right now. If anyone else wants to jump in then go for it.

 

[livvie]

suprervi.
Can you tellme how you did the following

When the user clicks the duplicate record button on the form, the Ticket Number stays the same but the Version Number" primary key increments by 1 (which is what we want). The Version Number is currently AUTONUMBER. At this point everything seems good however their are a few problems
??
I cannot get this to work.