Basic question

[Guest]

I'm not sure it's always so hard to find help on common coding, so here is my
question. I am using Access 2002 and I have a database I use for work. It
has a main table, and I have a form that allows a user to edit the records in
the main table. I would like to create a change log by having an "on update"
event launch that copies the pre-update values from the table to a new table
(with one more field - data changed) before updating (effectively a stack of
old records that have since been modified).

1 - is this possible? or will the data already have been updated?

2 - I am having trouble using code to modify the table... let alone figure
out how to edit the table. I've tried:

Private Sub Form_BeforeUpdate()
Dim db As Database
Set db = CurrentDb
Dim rs As Recordset
Set rs = db.OpenRecordset("Models_Change_Backup")
rs.AddNew
...
End Sub

It locks up at the openrecordset piece. I tried using OpenTable, but it
says that method is restricted or something. The name of the table is
spelled properly, and I have no idea how to refer to it so I that I can add a
new row, copy and paste values into it, add a date value, update, and close,
<all behind the scenes/invisible> whenever the user makes edits and changes
records or closes the form.

All help is greatly appreciated!

Thanks,
Andrew

 

[Dirk Goldgar]

I'm not sure it's always so hard to find help on common coding, so
here is my question. I am using Access 2002 and I have a database I
use for work. It has a main table, and I have a form that allows a
user to edit the records in the main table. I would like to create a
change log by having an "on update" event launch that copies the
pre-update values from the table to a new table (with one more field
- data changed) before updating (effectively a stack of old records
that have since been modified).

1 - is this possible? or will the data already have been updated?

You'll find an example -- probably code you can use directly -- here:

http://allenbrowne.com/AppAudit.html

2 - I am having trouble using code to modify the table... let alone
figure out how to edit the table. I've tried:

Private Sub Form_BeforeUpdate()
Dim db As Database
Set db = CurrentDb
Dim rs As Recordset
Set rs = db.OpenRecordset("Models_Change_Backup")
rs.AddNew
...
End Sub

It locks up at the openrecordset piece.

"Locks up"? Or does it give you a compile error concerning a type
mismatch? If the latter, you probably need to change this declaration:

Dim rs As Recordset

.... to this:

Dim rs As DAO.Recordset

And if that doesn't compile, you need to add a reference to the
Microsoft DAO 3.6 Object Library.