467,088 Members | 1,297 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,088 developers. It's quick & easy.

Complex Copy Function

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
  • viewed: 1824

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Peter Olsen | last post: by
5 posts views Thread by Justin Caldicott | last post: by
21 posts views Thread by Blair | last post: by
116 posts views Thread by Mike MacSween | last post: by
7 posts views Thread by silversurfer2025 | last post: by
17 posts views Thread by Julian V. Noble | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.