BeforeUpdate, want to copy all pre-update fields to backup tbl - h

[Guest]

I have a main table, and a form a user can use to modify records in the main
table. When a user modifies a record, I want to get the pre-update
(BeforeUpdate) data copied into a backup table with one more field (date of
update). This will serve as a simple audit log. Here is how I have it thus
far:

Private Sub Form_BeforeUpdate()
Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Models_Change_Backup")
rs.AddNew
rs![ChangeDate] = Now
<NEED HELP HERE>
rs.Update
End Sub

In the <NEED HELP HERE> section, I'd like to have something like rs!* = Me!*
to copy all of the data from the current record to the new row of the backup
table. Keep in mind the new table has one more column, which is why I have
the rs![ChangeDate] = Now line.

Any thoughts on a quick way to copy the 100 fields without writing 100 lines
of code?

Thanks!

Andrew

 

[Brendan Reynolds]

Untested air-code follows. This assumes that the fields in the target table
have the same names as the fields to which the controls are bound.

Dim ctl As Control
Dim prp As Property
....

For Each ctl In Me.Controls
For Each prp In ctl.Properties
If prp.Name = "Control Source" Then
rst.Fields(prp.Value) = ctl.Value
Exit For
End If
Next prp
Next ctl

 

[Bill Edwards]

In the "Models_Change_Backup" table make the default value of "ChangeDate" =
now()
In the before update event of the form put something like:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb()
strSQL = "INSERT INTO Models_Change_Backup SELECT MainTableName.* FROM
MainTableName WHERE MainTableName.KeyValue = " & Me.KeyValue
db.Execute strSQL

This ignores the fact that you have a 100 field table design which implies
it is not normalized, so you will probably have bigger problems to worry
about down the road.

 

[Guest]

Thanks! Worked like a charm. 100 was a slight exaggeration, and I'm only
making a demo for a future build, so I should be okay. I just needed
something that does what the final version should be doing.

In the "Models_Change_Backup" table make the default value of "ChangeDate" =
now()
In the before update event of the form put something like:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb()
strSQL = "INSERT INTO Models_Change_Backup SELECT MainTableName.* FROM
MainTableName WHERE MainTableName.KeyValue = " & Me.KeyValue
db.Execute strSQL

This ignores the fact that you have a 100 field table design which implies
it is not normalized, so you will probably have bigger problems to worry
about down the road.

I have a main table, and a form a user can use to modify records in the
main
table. When a user modifies a record, I want to get the pre-update
(BeforeUpdate) data copied into a backup table with one more field (date
of
update). This will serve as a simple audit log. Here is how I have it
thus
far:

Private Sub Form_BeforeUpdate()
Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Models_Change_Backup")
rs.AddNew
rs![ChangeDate] = Now
<NEED HELP HERE>
rs.Update
End Sub

In the <NEED HELP HERE> section, I'd like to have something like rs!* =
Me!*
to copy all of the data from the current record to the new row of the
backup
table. Keep in mind the new table has one more column, which is why I
have
the rs![ChangeDate] = Now line.

Any thoughts on a quick way to copy the 100 fields without writing 100
lines
of code?

Thanks!

Andrew

 

[Van T. Dinh]

Allen Browne has a sample database "AppAudit" that may be of some use to
you.

http://allenbrowne.com/AppAudit.html