Copy associated records

[Derek]

On the main form I have a duplicate or copy button. This
copies the current records by means of an append query and
appends it to the underlying table and moves the focus on
the form to the new appended record. This allows the
customer to copy a record and make the minor changes
required. But there are other associated record from
another form and table that also need to be copied in a
simular way. There is referencial integraty between the
two tables that prevent me using an append query.
Help!!

 

[Scott McDaniel]

You would have to (a) determine what, if any, child records are associated
with the record you are copying and (b) build Append routines that would
also append those AFTER first building the "parent" record. You likely can't
use straight SQL for this, as you would have to add the parent record,
capture the ID of that record, and use it when inserting the child records.

 

[John Vinson]

On the main form I have a duplicate or copy button. This
copies the current records by means of an append query and
appends it to the underlying table and moves the focus on
the form to the new appended record. This allows the
customer to copy a record and make the minor changes
required. But there are other associated record from
another form and table that also need to be copied in a
simular way. There is referencial integraty between the
two tables that prevent me using an append query.
Help!!

Referential integrity shouldn't prevent you using an append query if
you do it in the right order; append the record for the mainform's
table first, and then the related records. If you run the two Append
queries from code in that order, they should work.

This is a bit of a risky design, since it means you're deliberately
storing redundant data - just be sure to have some way to be sure
you're not creating lots of duplicate (except for the Autonumber)
records and just leaving them there!

 

[Derek]

My Thanks to you both.

I do not see how it is possible. If I create a query that
uses linked tables Access onlyt alows me to choose a
single table too apend to, not to another query as I would
like then related tables do not matter.

Thank you anyway.

 

[John Vinson]

I do not see how it is possible. If I create a query that
uses linked tables Access onlyt alows me to choose a
single table too apend to, not to another query as I would
like then related tables do not matter.

To append data to multiple tables, you need to use multiple append
queries - parent table first, then the related tables.

The tables you're appending from can be local or linked; and you can
run the multiple queries from a Macro or from code (ideally, from code
wrapped in a Transaction to ensure that they either all get done or no
change gets made).