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
following:
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!