Creating records in one file whilst editing another file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

88Caddy - 11 Jun 2007 01:37 GMT

I have a simple form which allows updates to records in an employee
file. Each time a record is changed I would like to create an audit
record in a separate file. I do not need any input from the user when
creating the record as all the information is implied (date, time,
user, before and after values etc.) I would be interested to know
what people think the easiest way to do this would be...?? All
opinions welcome - the more the merrier.

Thanks, Jon.

Reply to this Message

Marshall Barton - 11 Jun 2007 13:12 GMT

>I have a simple form which allows updates to records in an employee
>file. Each time a record is changed I would like to create an audit
>record in a separate file. I do not need any input from the user when
>creating the record as all the information is implied (date, time,
>user, before and after values etc.)

You could construct an Append query to put the data into the
audit table (file?), but It might be easier to write the
code to open a recordset and add the record.

If the audit table is in the same mdb file and depending on
the structure of the audit table, the code could be along
these lines:

Set rs = CurrentDb.OpenRecordset("table name")
rs.AddNew
    rs!chgdate = Now
    rs!user = "user name"
    rs!fieldname = "field"
    rs!before = Me.field.OldValue
    rs!after = Me.field
rs.Update
rs.Close : Set rs = Nothing

Signature

Marsh
MVP [MS Access]

Reply to this Message