[Recordset]

[Nico]

Hi all,

I made a table with a numAuto field as a primary key. I would like to copy to keep track of my records, so I have decided that evrytime a record is changed in my form, I will create a new record having a field containing the ID of the old record.
In order to do that, I am trying to copy the recordset of my form to a new one, so it will be assigned a new numAuto ( I don't know the english term for the number type that automatically increases !).
But I can't figure out how !

Thanks in advance for your help !

Nico

 

[Allen Browne]

Hi Nico

Whenever the user changes a record, you want to create a new record with
these changes, and then cancel the changes to leave the old record in place?

Use the BeforeUpdate event of the form to AddNew to the form's
RecordsetClone, and then cancel and undo the update. Here is a rough bit of
aircode to demonstrate the idea:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Me.NewRecord Then
With Me.RecordsetClone
'Create the new record
.AddNew
![SomeField] = Me.[SomeField]
![AnotherField] = Me.[AnotherField]
![OldnumAuto] = Me.[numAuto]
'and so on for your other fields.
.Update
.Bookmark = .LastModified
'Cancel the update
Cancel = true
Me.Undo
'Move to the new record
Me.Bookmark = .Bookmark
End With
End If
End Sub


Another approach is to write the history of previous records to *another*
table, so you have a complete and separate log of edits, inserts, and
deletes. If you are interested in that approach, see:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

(The English term for your numAuto field is AutoNumber.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I made a table with a numAuto field as a primary key. I would like to copy

to keep track of my records, so I have decided that evrytime a record is
changed in my form, I will create a new record having a field containing the
ID of the old record.

In order to do that, I am trying to copy the recordset of my form to a new

one, so it will be assigned a new numAuto ( I don't know the english term
for the number type that automatically increases !).