Cloning records from a form with subforms

[rafi2]

Hi,

Can someone help me with this... I've got a main form with 6 subforms. The main form is associated with the "one-side" table. Each subform is associated with its own "many-side" table. What I would like to do is be able to click a button on the main form to make a copy of the record and all associated records in the "many-side" tables, but obviously with the new records having a different primary key. What is the best way to go about doing this?

Thanks!

 

[ThreeCrow]

You could use an AppendQuery, based on the underlaying Queries of mainForm and subForms.

But why are you wanting to duplicate Records.

 

[rafi2]

Thanks for the info. In the end, the records will not be exact duplicates. This copying functionality would be useful in instances where the user knows that a record will be mostly the same as an exisiting one, so that they will only need to make a few changes, rather than enter everything from the beginning.

Here's what my plan is. Can someone please tell me if this is the correct way of going about it?

To use an Append Query, I'm thinking I would first need to copy the exisiting values to a temporary table via a Make Table query. I would then run an Update Query on this temporary table to change the primary key to the new value that I need. I would then append this temporary table to the "real" table. I would then need to flush the values out of this temporary table via a Delete Query so that it is free for the next time I need it. And because I have the Main Table and six associated Sub Tables, I would need to run through this procedure for each one.

Does this sound like a good approach or is there a way that is less convoluted?

Thanks!

 

[neileg]

Sounds ok. Two points, though.

You don't want a make table query. Set up the temporary table only once (the table isn't temporary but the data is). Then append your data.

If your PK is an autonumber, you don't calculate it. When you append your new record, the autonumber will be assigned, automatically.

 

[rafi2]

Thanks, Neil.

My PK is not an autonumber. It would have to be supplied by the user. This would be via an unbound text box on the form.

As for the VB code behind this, once I've set up my queries, would I just use a series of DoCmd.OpenQuery "queryName" statements?

And one more thing I would like to do at the end of the code is to have the form display the new record, but I am unsure of the coding for this. Can I kindly ask for some help with that?

Thanks again!

 

[neileg]

Thanks, Neil.

My PK is not an autonumber. It would have to be supplied by the user. This would be via an unbound text box on the form.

It is very brave of you to allow the user to assign the PK. Can I suggest that you rethink this. By all means allow the user to assign a unique reference number, but you should use your own PK, preferably an autonumber.

the VB code behind this, once I've set up my queries, would I just use a series of DoCmd.OpenQuery "queryName" statements?

Yes

And one more thing I would like to do at the end of the code is to have the form display the new record, but I am unsure of the coding for this. Can I kindly ask for some help with that?

Open the form and move to the last record.

 

[rafi2]

Open the form and move to the last record.

What would be the actual VB code for "move to the last record"?

 

[ThreeCrow]

Private Sub Form_Load()

DoCmd.GoToRecord acDataForm, Me.Name, acLast

End Sub