Need Help Superseding a Record in a Relational Database

[dalecorey1 via AccessMonster.com]

I have a form with several subforms that help create a formal report for
control board directives. I am trying to find a simple way to "supersede" a
record so that the administrator can revision (the original revision number
is bumped up by one) the directive with the original data and then edit
whatever data has changed from the original version. Each revision of the
directive has to be kept as is for historical purposes (signatures are
required).

I have a solution for another control board directive but it is very detailed.
Basically, the user clicks a "supersede" button and temporary make tables are
created with all the original data. Then a form and its subforms are brought
up with all the temporary make table data and the revision number is bumped
up by one. When the administrator is satisfied with the new changes, they
click save and APPEND queries are ran to append the data into the production
copy of the relational database.

I played around with "Duplicate" record but it didn't work 'cause of the
primary key violation.

Any ideas to make this easier?

TIA,

dalecorey1

 

[Klatuu]

I don't think you need to have a separate table. The is a different way to
do this. The question is the primary key problem. In a situation like this,
the primary key should be an Autonumber surrogate key that has no meaning to
the data.

The concept is to make the Default Value properties of all your form
controls that contain data, execpt the primary key field, the value of the
current record. Then add a new record. You can do this in the Click event
of a command button.

With me
.txtSomeControl.DefaultValue = .txtSomeControl
.txtAnotherControl.DefaultValue = .txtAnotherControl
.txtDirective.DefaultValue = .txtDirective
.txtRevisionNumber.DefaultValue = Nz(DLookup("[Rev_no]",
"SomeTable", "[Directive] = " & .txtDirective),0) +1
End With
Docmd.GoToRecord acNewRec

If the subforms are involved with this, you would also have to do the same
for each of them.