Recordset automatically updates

[NevilleT]

I am updating some records using a standard VBA function. I want to create a
history record to show the old and new values. The approach I used was to
create a recordset before the update (rstBefore), and one after the update
(rstAfter). I can then loop through the records looking for changed values
and when I find them, write the changes to a history table. In the example
below, I am just printing the changes.

THE PROBLEM: The before recordset is fine until I run the update. The old
recordset then updates itself for some reason to the new values. Whilst
there is a lot of code, the key parts are below.

Create the before recordset. I am using a simple SQL statement:
Set dbs = CurrentDb
Set rstBefore = dbs.OpenRecordset(strSQLHistory)

Update the data

Create the after recordset:
Set rstAfter = dbs.OpenRecordset(strSQLHistory)

Do the compare on a boolean value called "Read":
While Not rstBefore.EOF
strCriteria = "[UserAccessNo] = " & rstBefore!UserAccessNo
rstAfter.MoveFirst
rstAfter.FindFirst strCriteria

If rstAfter!Read <> rstBefore!Read Then
Debug.Print rstBefore!Read & " " & rstAfter!Read
End If

rstBefore.MoveNext
Wend

Can anyone suggest how I stop rstBefore being recreated, or suggest another
path?

 

[Allen Browne]

If the recordset is referring to the same actual data, that approach won't
work.

Instead, write the record to a temporary table in Form_BeforeUpdate. Then in
Form_AfterUpdate (which only fires if the update succeeded), you can compare
the values to those in the temp table.

The basics of this approach can be found here:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html
It doesn't actually do the field-by-field comparsion, but judging from your
post, you'll be fine with that.

 

[NevilleT]

Thanks Allen. Cannot use the form before update as I am actually doing this
with code. I have a datasheet which I update with a button. Select all the
textboxes and change the value to true programatically then requery the form.
I can explore your solution though. Just don't understand why the recordset
updates after I update the records.

If the recordset is referring to the same actual data, that approach won't
work.

Instead, write the record to a temporary table in Form_BeforeUpdate. Then in
Form_AfterUpdate (which only fires if the update succeeded), you can compare
the values to those in the temp table.

The basics of this approach can be found here:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html
It doesn't actually do the field-by-field comparsion, but judging from your
post, you'll be fine with that.

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

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

I am updating some records using a standard VBA function. I want to create
a
history record to show the old and new values. The approach I used was to
create a recordset before the update (rstBefore), and one after the update
(rstAfter). I can then loop through the records looking for changed values
and when I find them, write the changes to a history table. In the example
below, I am just printing the changes.

THE PROBLEM: The before recordset is fine until I run the update. The old
recordset then updates itself for some reason to the new values. Whilst
there is a lot of code, the key parts are below.

Create the before recordset. I am using a simple SQL statement:
Set dbs = CurrentDb
Set rstBefore = dbs.OpenRecordset(strSQLHistory)

Update the data

Create the after recordset:
Set rstAfter = dbs.OpenRecordset(strSQLHistory)

Do the compare on a boolean value called "Read":
While Not rstBefore.EOF
strCriteria = "[UserAccessNo] = " & rstBefore!UserAccessNo
rstAfter.MoveFirst
rstAfter.FindFirst strCriteria

If rstAfter!Read <> rstBefore!Read Then
Debug.Print rstBefore!Read & " " & rstAfter!Read
End If

rstBefore.MoveNext
Wend

Can anyone suggest how I stop rstBefore being recreated, or suggest
another
path?