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?

Neville,

A recordset does not contain records, it contains pointers to records. If
your records change, the data you get back from your recordset will show the
changed records.

If you're going to loop through the records to do the comparison anyway, why
don't you edit the records as you're looping through and write changes to the
history at the same time. Alternatively write another query (or function)
that will identify the changes up front and write these to the audit trail
first, and then update the records afterwards (all inside a workspace
transaction of course).

Jon.

"NevilleT" wrote:

> 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?