Sequential Copy-Paste????

[Access]

I have 4 tables

tbl0 tbl1 tbl2
tbl3
=== === === ===
PosDesc ID (1-oo) Ver DutyDesc
. Version duty (oo-1) ID
. Archive
. Date
ID (1-oo) Position

This means tbl2 is a many to many link table.
First tbl0 is filled with job positions.
then tbl3 is filled out with duties.
Then in a form (source tbl0) and in its subform of tbl1, a new version of
duties is created. Then in that version, in its subform, of tbl2, duties are
assigned to that specific version of that job position.
So its somehow like this:

Position: Administrator

(Duties List) Version: 1
Duties
.....
.....

When a new version is to be created, aka in tbl1, I want the current version
to be archived (Archive bit) and be copied to a new record with the next
version number so that the user won't actually have to reassign the duties,
but to add or remove the ones that changed.
Here lies the BIG PROBLEM.
Coping-Pasting the record from tbl1, leaves the many side of the
relationship empty. As if I just only selected the position from the combo
box.
I want to find a way that I can copy-paste not just the one side but the
many also.

Any help mostly appriciated as I am walking to a dead end.

Thanx in advance.

 

[Tim Ferguson]

When a new version is to be created, aka in tbl1, I want the current
version to be archived (Archive bit) and be copied to a new record
with the next version number so that the user won't actually have to
reassign the duties, but to add or remove the ones that changed.

Sorry: can I put some names on these things so they make a bit of sense?

Positions(*ID, PosDesc)

Versions(*ID, Version, Archive, VersionDate, Position)
FK Position references Positions

Assignents(*Ver, *Duty)
FK Ver references Versions
FK Duty references Duties

Duties(*ID, DutyDesc)


You can create a new Version using a simple Append query

INSERT INTO Versions
SELECT * FROM Versions WHERE ID = 2045

And get the new ID value in a hurry (ADO only: you'll need a different
trick if you have to use DAO).

SELECT @@IDENTITY

Then point the Assignment(s) from the old Version to the new Version

UPDATE Assignments
SET Ver = 3997
WHERE Ver = 2045

Finally, set the Archive bit if you want

UPDATE Versions
SET Archive = 1
WHERE ID = 2045

Copy-and-paste doesn't really come into it.

Hope that helps


Tim F