Undo Changes on a Main form and associated Sub Forms (7 Viewers)

[NauticalGent]

Reading this thread:

Solved - Undo Updates on a Subform | Access World Forums https://share.google/vcxiK5qDD0uyPyFVs

The solution provided involving temp tables seemed a little unwieldy.

I am wondering if a Class Module using Dictionaries, Collections or Arrays would be cleaner and/or more efficient. But before I start down that rabbit-hole, I am interested in hearing what others think of this...

 

[gemma-the-husky]

Do you mean design changes, or data edits?
Edit - looking at the previous thread, I take it data.

Do you mean reverse the change after the edit and update has taken place?

 

[The_Doc_Man]

The problem is the difference between Access updates and, say as an example, ORACLE updates.

If you turn on the appropriate option in ORACLE, you gain the ability to roll back an update because ORACLE keeps a change log. These change logs expire after some amount of time, but there is a type of rollback command you can give ORACLE to undo a committed update.

Access, on the other hand, doesn't have that option built-in. You CAN manually implement a roll-back level of logging manually, but you are on your own for how that will work. The last time I looked at it, the best roll-back-level option was to put something in the Form_BeforeUpdate event to scan the Form.Recordset.Fields collection to compare .Value and .OldValue, and to record the changes where there was a difference. Tedious and, even when you try to optimize it, a space-eater.

One man's viewpoint, certainly not gospel.

 

[NauticalGent]

Do you mean design changes, or data edits?
Edit - looking at the previous thread, I take it data.

Do you mean reverse the change after the edit and update has taken place?

Hi Dave,

Yes. Basically, something to capture the form's and subform's field values BEFORE and changes are made and then restoring them if the cancel button is pressed.

 

[isladogs]

Have a look at my coding modern charts example app demonstrated at the start of last weeks Access Europe meeting
In that example I use a collection to store values for each chart series as they are changed on the form. When the form is closed, by default the changes are discarded. Or you can click a Save button to store those changes for future use.

 

[NauticalGent]

Have a look at my coding modern charts example app demonstrated at the start of last weeks Access Europe meeting
In that example I use a collection to store values for each chart series as they are changed on the form. When the form is closed, by default the changes are discarded. Or you can click a Save button to store those changes for future use.

So, by this answer, you agree that using collections, dictionaries or arrays would be a better course of action?

 

[isladogs]

Probably so. However you still need to store the data if you want to reuse it e.g. to change the form/control design.

 

[CJ_London]

Assuming you are committing to cancel or save during the life of the open form -

For single forms comparing oldvalue and value is probably sufficient since data isn’t saved until the record is updated (form update event or forced with dirty)

For continuous forms consider using an ado disconnected recordset rather than a collection or dictionary. In my experience, easier to populate and manage.

Either way, can get complicated in a multi user environment and/or you want to save one change and cancel another

 

[sonic8]

If you turn on the appropriate option in ORACLE, you gain the ability to roll back an update because ORACLE keeps a change log. These change logs expire after some amount of time, but there is a type of rollback command you can give ORACLE to undo a committed update.

I believe you mean what Oracle calls Flashback Query. It appears to be very similar to Temporal Tables on Microsoft SQL Server.
However, I'm not sure if this the right tool for this job. In the main-form/sub-form context data of one form might need to be restored but not from other form(s). How to handle this cleanly in code? Also what happens with concurrent updates by other users?

The solution provided involving temp tables seemed a little unwieldy.

I am wondering if a Class Module using Dictionaries, Collections or Arrays would be cleaner and/or more efficient.

The problem is that Access is really dependent on data binding to tables/queries, at least for continuous/datasheet forms. If these types of forms are involved, temp tables are indeed a sensible approach.

An alternative would be to use transactions to only persist data changes explicitly confirmed by the user. - I'm not a big fan of using explicit user-controlled transactions. The risk of the user failing to commit (or rollback) and blocking the records for a long time is quite high in my experience.

 

[The_Doc_Man]

I believe you mean what Oracle calls Flashback Query.

When I retired, they didn't call it that, but in any case I was using ORACLE as a counter-example for what was required for Access. And I agree with the idea that depending on the user to make a rational decision about when to release a record might lead to a difficult situation.

 

[Ken Sheridan]

Yes. Basically, something to capture the form's and subform's field values BEFORE and changes are made and then restoring them if the cancel button is pressed.

I use the following module to determine whether the data in a record has or will actually change, rather than merely be updated:

' module basChangedRecord

' determines if data in a record edited
' in a form has actually been changed

Option Compare Database
Option Explicit

' arrays for storing values from recordsets
Dim aOldVals(), aNewVals()

' Boolean variable to restrict openig of report to button om form
Public blnOpenedFromButton As Boolean

Public Sub StoreOldVals(rst As DAO.Recordset)

   ' store values of current row in array
   aOldVals = rst.GetRows()
  
End Sub

Public Sub StoreNewVals(rst As DAO.Recordset)
  
   ' store values of edited row in array
   aNewVals = rst.GetRows()

End Sub

Public Function RecordHasChanged() As Boolean

   Dim n As Integer, intlast As Integer
   Dim var As Variant
   Dim aOld(), aNew()
  
   intlast = UBound(aOldVals) - 1
  
   ' loop through array of original values
   ' and store in new array
   ReDim Preserve aOld(UBound(aOldVals))
   For Each var In aOldVals()
       aOld(n) = var
       n = n + 1
   Next var
  
   n = 0
  
   ' loop through array of edited values
   ' and store in new array
   ReDim Preserve aNew(UBound(aOld))
   For Each var In aNewVals()
       aNew(n) = var
       ' if any value has changed then return True
       If (IsNull(aNew(n)) And Not IsNull(aOld(n))) _
           Or (Not IsNull(aNew(n)) And IsNull(aOld(n))) _
           Or aNew(n) <> aOld(n) Then
          
           RecordHasChanged = True
           Exit For
       End If
       n = n + 1
   Next var
  
End Function



Public Function ColumnWillChange(lngContactID, strColumnName As String, varColumnVal As Variant, dtmDateTimeStamp As Date) As Boolean

    Dim varNextDateTimeStamp As Variant
    Dim strCriteria As String
    
    strCriteria = "ContactID = " & lngContactID & " And DateTimeStamp > #" & Format(dtmDateTimeStamp, "yyyy-mm-dd hh:nn:ss") & "#"
    varNextDateTimeStamp = DMin("DateTimeStamp", "qryContactsAudit", strCriteria)
    
    If Not IsNull(varNextDateTimeStamp) Then
        strCriteria = "ContactID = " & lngContactID & " And DateTimeStamp = #" & Format(varNextDateTimeStamp, "yyyy-mm-dd hh:nn:ss") & "#"
        If DLookup(strColumnName, "qryContactsAudit", strCriteria) <> varColumnVal Then
            ColumnWillChange = True
        End If
    End If
    
End Function

This forms the basis of the following demo form in which rows are written to an audit log table if any of the data has changed:

The highlighting of the column positions where data has changed in the next row inserted is done by means of conditional formatting using an expression such as:

ColumnWillChange([ContactID],"LastName",[LastName],[DateTimeStamp])

which calls the ColumnWillChange function in the above module.

This only logs changes to the parent form's table of course, but it should be possible to apply the same methodology to both the parent form and subform, and write rows to an audit log table which is a join of the parent and subform's tables. To restore the parent and subform to earlier data would then involve returning the relevant rows from the audit log table and decomposing them into a single row to be inserted into the referenced table, and a set of rows into the referencing table, replacing the existing rows.