If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Recordset automatically updates
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? |
#2
|
|||
|
|||
Recordset automatically updates
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 he 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "NevilleT" wrote in message ... 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? |
#3
|
|||
|
|||
Recordset automatically updates
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. "Allen Browne" wrote: 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 he 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "NevilleT" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|