Solved Undo Updates on a Subform (1 Viewer)

Kayleigh

Member
Local time
Today, 09:34
Joined
Sep 24, 2020
Messages
706
I know there are loads of suggestions on this problem but I am looking for a simple solution to implement.
The structure of the forms are very basic (both single forms):
Parent form = details of Tenancy
Child form = details of Tenant
I would like to have a cancel button on parent form which would reverse all updates on both these forms.
Code I tried was:
Code:
If MsgBox("Are you sure you wish to discard changes?", vbOKCancel + vbQuestion, "LuxerMan") = vbOK Then
    Me.Undo
        Dim ctrl As Control

    For Each ctrl In Me.Controls
        If ctrl.ControlType = acTextBox Then
           ctrl.Value = ctrl.OldValue
        End If
    Next
    DoCmd.Close acForm, Me.Name
Else
    Exit Sub
End If
But error flagged up ctrl.value = ctrl.oldvalue that it can't assign value to this field??
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:34
Joined
Oct 29, 2018
Messages
21,357
For one thing, I think OldValue only applies to bound controls. For another, I am not sure if it's only available in the BeforeUpdate event. If you must have this feature, you might consider using a temp table to store all edits/changes to the data and then use two buttons: commit (save) and discard (cancel) changes. Just a thought...
 

Kayleigh

Member
Local time
Today, 09:34
Joined
Sep 24, 2020
Messages
706
Yes I thought the same. That is why I am exploring other approaches.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:34
Joined
Oct 29, 2018
Messages
21,357
Yes I thought the same. That is why I am exploring other approaches.
What other approaches are you trying? When using bound forms, the record is automatically saved to the table once you go from main to subform and back.
 

Kayleigh

Member
Local time
Today, 09:34
Joined
Sep 24, 2020
Messages
706
Would unbound controls work?
There are only 8 text fields on the subform...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:34
Joined
Oct 29, 2018
Messages
21,357
Would unbound controls work?
There are only 8 text fields on the subform...
Yes, unbound controls could work, but probably not by using the OldValue property.
 

Kayleigh

Member
Local time
Today, 09:34
Joined
Sep 24, 2020
Messages
706
Maybe if I would save the old vals as an array in the form - afterUpdate, I could revert to these values if undo is called?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:34
Joined
Oct 29, 2018
Messages
21,357
Maybe if I would save the old vals as an array in the form - afterUpdate, I could revert to these values if undo is called?
Right. You have two options:

1. Keep using a bound form and save the old values somewhere else, or
2. Use an unbound form and only commit the changes after confirmation - the old values stay in the original table.

Good luck!
 

Kayleigh

Member
Local time
Today, 09:34
Joined
Sep 24, 2020
Messages
706
Hi @theDBguy
I am trying your suggestion of using a bound form and saving the old values dynamically.
I tried the following approach but it is not working at all. Am I missing something here?

Declared a public array:
Code:
Public oldVals(8) As Variant

Each value is assigned to one element in the array if updated:
Code:
oldVals(1) = Me.fldTFirstName.OldValue

The undoVals is a public sub on child form:
Code:
Public Sub undoVals()

Dim i As Variant
For Each i In oldVals
Debug.Print i
Select Case i
Case 0
If Not IsNothing(oldVals(0)) Then Me.fldTenantID = oldVals(0)
Case 1
If Not IsNothing(oldVals(1)) Then Me.fldTFirstName = oldVals(1)
Case 2
If Not IsNothing(oldVals(2)) Then Me.fldTLastName = oldVals(2)

Case 3
If Not IsNothing(oldVals(3)) Then Me.fldTAddress = oldVals(3)

Case 4
If Not IsNothing(oldVals(4)) Then Me.fldTHomePhone = oldVals(4)

Case 5
If Not IsNothing(oldVals(5)) Then Me.fldTWorkPhone = oldVals(5)

Case 6
If Not IsNothing(oldVals(6)) Then Me.fldTMobilePhone = oldVals(6)

Case 7
If Not IsNothing(oldVals(7)) Then Me.fldTNotes = oldVals(7)


End Select
Next
End Sub

I call the undoVals sub from the main form like this:

Code:
Private Sub cmdCancel_Click()
If Me.Dirty Then
If MsgBox("Are you sure you wish to discard changes?", vbOKCancel + vbQuestion, gtstrAppTitle) = vbOK Then
    Me.undo
    Call Me.frmTenancyTNSF.Form.undoVals
    DoCmd.Close acForm, Me.Name
Else
    Exit Sub
End If
Else
DoCmd.Close acForm, Me.Name
End If
End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:34
Joined
Feb 19, 2002
Messages
42,970
When focus moves from the main form to the subform, the main form record is saved. This is NECESSARY because in a relational database, the parent record MUST exist before you can add a related child record. When focus moves from the subform to the mainform, the subform record is saved. Therefore, under NO conditions are the mainform and subform ever dirty at the same time. Therefore with bound forms, you cannot do precisely what you are asking.

So, I ask you, WHY is this a requirement because you are going to have to write a lot of code and use unbound forms to make this happen? If the user wants to get rid of both the parent and child records, he can delete the parent and if you have enabled cascade delete, this will delete all related child records.

Are you assuming that your users are too stupid to understand how to do the data entry correctly?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:34
Joined
Oct 29, 2018
Messages
21,357
Hi @theDBguy
I am trying your suggestion of using a bound form and saving the old values dynamically.
I tried the following approach but it is not working at all. Am I missing something here?

Declared a public array:
Code:
Public oldVals(8) As Variant

Each value is assigned to one element in the array if updated:
Code:
oldVals(1) = Me.fldTFirstName.OldValue

The undoVals is a public sub on child form:
Code:
Public Sub undoVals()

Dim i As Variant
For Each i In oldVals
Debug.Print i
Select Case i
Case 0
If Not IsNothing(oldVals(0)) Then Me.fldTenantID = oldVals(0)
Case 1
If Not IsNothing(oldVals(1)) Then Me.fldTFirstName = oldVals(1)
Case 2
If Not IsNothing(oldVals(2)) Then Me.fldTLastName = oldVals(2)

Case 3
If Not IsNothing(oldVals(3)) Then Me.fldTAddress = oldVals(3)

Case 4
If Not IsNothing(oldVals(4)) Then Me.fldTHomePhone = oldVals(4)

Case 5
If Not IsNothing(oldVals(5)) Then Me.fldTWorkPhone = oldVals(5)

Case 6
If Not IsNothing(oldVals(6)) Then Me.fldTMobilePhone = oldVals(6)

Case 7
If Not IsNothing(oldVals(7)) Then Me.fldTNotes = oldVals(7)


End Select
Next
End Sub

I call the undoVals sub from the main form like this:

Code:
Private Sub cmdCancel_Click()
If Me.Dirty Then
If MsgBox("Are you sure you wish to discard changes?", vbOKCancel + vbQuestion, gtstrAppTitle) = vbOK Then
    Me.undo
    Call Me.frmTenancyTNSF.Form.undoVals
    DoCmd.Close acForm, Me.Name
Else
    Exit Sub
End If
Else
DoCmd.Close acForm, Me.Name
End If
End Sub
Hi. I think I mentioned this earlier, but the old values are only available until they get replaced by the new values. So, you could be using the Form's BeforeUpdate event to store the old values. I'm not sure when you are doing that, but try using the Form's BeforeUpdate event. Also, by storing them in memory, those values will either disappear after the variable goes out of scope or gets replaced by the next record changes made by the user. So, I am wondering when you want to affect the "undo" part of your database; because if you do it at the wrong time, it could be too late - the old values are gone, or worse, they're the wrong old values for the current record.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:34
Joined
Feb 19, 2002
Messages
42,970
The .OldValue changes from the previous value to the current value between the FORM's Before and After Update events.
I'm not sure if variables stay in scope in the main form when focus moves to the subform.
 

Solo712

Registered User.
Local time
Today, 05:34
Joined
Oct 19, 2012
Messages
828
The .OldValue changes from the previous value to the current value between the FORM's Before and After Update events.
I'm not sure if variables stay in scope in the main form when focus moves to the subform.
Public variables are accessible from the subform via Parent.varname.

Jiri
So, I ask you, WHY is this a requirement because you are going to have to write a lot of code and use unbound forms to make this happen? If the user wants to get rid of both the parent and child records, he can delete the parent and if you have enabled cascade delete, this will delete all related child records.

I am with you on this. What's more is that the tenant data is not dependent on the tenancy data so the requirement the two should be updated at the same time is suspect. The main-sub setup allows reassignment of the tenancy agreement to a new tenant by simply changing the FK or even making it a Null value. (In case this is not allowed, the program can proceed they way you suggest). The updates in both, the main form and the subform can be undone before leaving simply by pressing the Esc key. Finally, if the user wants a control over a saved record on both forms, he may want to create a save button and add this logic to the modules:

Code:
Option Compare Database
Option Explicit

Public Commit As Boolean
'--------------------'
Private Sub Form_BeforeUpdate(Cancel As Integer)
   If Not Commit Then
     Cancel = True
   End If
End Sub
'--------------------'
Private Sub Form_Current()
  Commit = False
End Sub
'---------------------------'
Private Sub SaveButton_Click()
  If Me.Dirty Then
       Commit = True
       Me.Dirty = False
       'DoCmd.Save acForm, "frmTenancy"
  End If
End Sub
 

Pac-Man

Active member
Local time
Today, 14:34
Joined
Apr 14, 2020
Messages
408
Hi,

This is an old thread but I am posting in it so that if someone else is looking for undo in subforms, he might find this useful.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:34
Joined
Feb 28, 2001
Messages
26,996
Thank you, Pac-Man, because you popped it into my attention.

Pat said:
The .OldValue changes from the previous value to the current value between the FORM's Before and After Update events.
I'm not sure if variables stay in scope in the main form when focus moves to the subform.

Jiri (Solo712) said:

Public variables are accessible from the subform via Parent.varname.

For our newer members who find this an interesting topic (as Pac-Man did), I will expand slightly on this point.

Normally, Access form/report events are entered into a queue and their associated event routines are executed in the order that they were placed in the queue. An event routine CANNOT interrupt another event routine except in EXTREMELY rare circumstances. That means that in 99.9+% of the cases, event code is single-threaded and "linearized" (one routine at a time). An event routine runs because it's triggering event occurred AND all previous event routines have exited, i.e. nothing ahead of the routine in the queue. If any variables are declared local to (within) that event routine, they are created by entering (calling, activating) the routine and released by End Sub/Exit Sub.

For a form (class) module, items in the class module declaration area will persist until the form is closed. For a general module, items in the declaration area can persist while the DB is open. Public items can, of course, be seen from anywhere. If debugging, the dreaded debug RESET option releases the items.

For the original question (holding values in variables for an UNDO operation), the factors to consider are:

a) Scope of the variable - must be public (any module) or must be in the declaration area of the same class module holding the executing code, but CANNOT be local to any event routine.
b) Lifetime of the variable - general module declaration area longest (lifetime of the Access session); then class module declaration area next (lifetime of the form or report); then class module local is shortest (duration of the event routine)
c) All bets are off after a Debug/RESET operation.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:34
Joined
Feb 19, 2002
Messages
42,970
The updates in both, the main form and the subform can be undone before leaving simply by pressing the Esc key.
Not true. The data on the main form has already been saved by the time focus moves to the subform so there is NO WAY that you can back up to the main form and then use escape to back out changes. It is just not possible without temp tables and complicated logic. PERIOD.

Although I did see a class module that claimed to handle this posted by one of our members. I did not test it so I can't say whether it is designed to handle anything except initial adds. There is also the link that resurrected this thread. I didn't test that either. I've never had a group of users that couldn't with a little guidance manage to enter a coherent set of data. So, I've never needed to pursue this. If I were to do it, I would use temp tables and add a flag that locked the parent record which would prevent anyone else from even updating it while a change to the set was going on. I would even change the form background to warn the user that some unknown change was taking place and to not rely on the data for anything until the change was complete.
 

Pac-Man

Active member
Local time
Today, 14:34
Joined
Apr 14, 2020
Messages
408
Hello,

The link I posted in my former reply is tested by me and I'm using it in my projects and it is quite simple. It uses a temp table to hold the record until the form is closed. If undo is required data from temp table is copied back into the main record to undo. If the changes are required to be committed instead of undo, then record being edited is simply saved and record in the temp table is deleted. Demo in the link uses the same backend to create temp table whereas I create a local temp db on each of workstation (automatically by VBA) and use that to hold and delete temp takes and records.
I would use temp tables and add a flag that locked the parent record which would prevent anyone else from even updating it while a change to the set was going on.
However approach in the link doesn't cover this scenario. There could be issues if two users are editing the very same record. It never occurred to me before, I have to work on it too. Thanks @Pat Hartman for the insight.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:34
Joined
Feb 19, 2002
Messages
42,970
Does the code handle updates, including add/delete of the subform records?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:34
Joined
Feb 19, 2002
Messages
42,970
Updates are a whole different problem. You need two sets of shadow tables for them. One to save the original values and a second to hold the changes.
 

Pac-Man

Active member
Local time
Today, 14:34
Joined
Apr 14, 2020
Messages
408
Updates are a whole different problem. You need two sets of shadow tables for them. One to save the original values and a second to hold the changes.
Yes, I have been using it for atleast two and half years without any problems. Yes the code handles add/edit and deletes. Algorithm being used as below:
When the form is fully loaded, code create each form's (including subforms) recordsource table as Temp_TableActualName and copies the data from tables into each temp table. Then the record is being edited or added is being worked on as usual as if no undoing code exists. When you close the form then it happens again as if there is no undo code exists and the data from temp tables is deleted. But if you press undo button then code copies all the data from temp tables to the actual tables. Only care is recordsource of forms must be a table not a select statement and secondly subforms can't have Autonumber field because seed value is set to the last record's id so I am using custom code on form AfterUpdate event for Autonumber which locks the tblAutoNum (table that keeps track of last id number for specific field) and get next available number and release the table for other users to avoid conflict in case of multi user environment.

Another issues that I found lately are:
1) described by @Pat Hartman that in case of multi user environment, if two users are editing the same record then there may be problem. I didn't face this issue in my case rill now and don't expect it to happen too I'll try to resolve it by locking the record for other users if it is being edited. I don't have idea how to do it but will try.
2) I recently added audit trail option in my project, and by moving from main form to subform, records are committed and also recorded into audit trail but if the undo button is pressed, records is inverted back to its original values but audit trail has the log of that undoed modification but this doesn't seem like a problem to me right now but may be I try to figure out something for this in future.
 
Last edited:

Users who are viewing this thread

Top Bottom