By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,594 Members | 3,680 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,594 IT Pros & Developers. It's quick & easy.

Complex Copy Function

P: n/a
I have a database with a Header table. Each record in tblHeader has
two One-to-Many Relationships: with tblLines and tblKeys. The HeaderID
field ties tblHeader to the other two tables. The data collected in
tblHeader is simple: HeaderID (unique), a text description field, and a
date field.

I need to be able to copy header and it's related info.

I have a form with a "Copy Source" combo box allowing the user to
select a Header record. Then two text fields allowing the user to
input a new description and date. Then a button saying "Copy Header".

What I need to do is assign VBA code to that button that does the
1. Insert a new record into tblHeader, which will obviously receive a
new HeaderID, plus add the new Description and Date values (This part
is no problem).
2. Copy all records in tblLines and tblKeys that had a HeaderID
matching the "Copy Source" Header record. Paste duplicates in those
two tables, but when I paste, change the HeaderID value, so that all
the pasted records now tie to the new record in tblHeader.

The result is two Header records in tblHeader, with different
Description and Date fields, but having identical related records in
tblLines and tblKeys.

If the reason helps, we often have one Header record with hundreds of
Line and Key records tied to it. Sometimes we need to basically repeat
an exact entry, with a Key or Line change or two. What we want to do
is copy an existing record and all it's cascading related records under
a new Description, and then just edit the couple lines we need, instead
of having to recreate the whole thing.

Hope this makes sense. I have a few ideas on how to do this, but I was
hoping before I get halfway in and realize I should do it another way,
I'd get some expert advice on the best way to approach this. Thanks!

Feb 27 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.