Solved

Undo changes to a subform - Access 2003 VBA

Posted on 2009-05-06
15
2,858 Views
Last Modified: 2013-11-27
Hi all, I hope you can help me!

I have an Access database, and part of the database is what you might call an audit log. When a user makes a change to value, a form pops up detailing the change and requests the reason for the change.

The main form consists of two subforms, which again when a value is changed, a pop up requests further information. Lets say the forms are called:-

MainForm
Subform1
Subform2

The code to run the 'audit' procedure is set to run on the BeforeUpdate event of all three forms. This way, I am able to capture the old value and the new value for the audit table.

Now for my problem! On the pop up form, I have an OK and Cancel button. The OK button commits the data to the audit table and also commits the changes made to the MainForm, Subform1 or Subform2, and this works fine. For the Cancel button, I want it to undo the changes on the pop up form, which works, and also undo the changes on the MainForm, Subform1 or Subform2, which doesn't work.

Ideally I would like it to be able to undo a specific field the MainForm, Subform1 or Subform2, but if I can just undo the whole record that would suffice. I have attached the code I am using to try and get this working, the AuditTrailMod is called by the BeforeUpdate event, the other code is the OK and Cancel buttons on the Audit form.

Any help would be much appreciated.

Regards,

Mark
Public strActControlName As String
 
Sub AuditTrailMod(frm As Form, RecordID As Control)
 
  'Track changes to data.
  'recordid identifies the pk field's corresponding
  'control in frm, in order to id record.
  Dim ctl As Control
  Dim varBefore As Variant
  Dim varAfter As Variant
  Dim strInfo As String
  Dim strCategory As String
  Dim strDescription As String
  Dim strControlName As String
  
  On Error GoTo ErrHandler
  'Get changed values.
  For Each ctl In frm.Controls
    With ctl
    'Avoid labels and other controls with Value property.
    If .ControlType = acTextBox Or .ControlType = acComboBox Then
      If .Value <> .OldValue Then
        varBefore = .OldValue
        varAfter = .Value
        
        strControlName = .StatusBarText
        strActControlName = .Name
        
        If .StatusBarText = "Connected To" Then
            varBefore = DLookup("Switch", "Switches", "SwitchID=" & .OldValue)
            varAfter = DLookup("Switch", "Switches", "SwitchID=" & .Value)
        End If
                             
        Select Case frm.RecordSource
            Case "NetCards"
                strInfo = "You are about to change the " & strControlName & " of Network Adapter " & Forms![Machines]![NetCardsSubform].Form!Adapter
                strCategory = "Network Card"
                strDescription = "The " & strControlName & " on Network Adapter " & Forms![Machines]![NetCardsSubform].Form!Adapter & " was changed from " & _
                    varBefore & " to " & varAfter
            Case "Drives"
                strInfo = "You are about to change the " & strControlName & " of Disk " & Forms![Machines]![DrivesSubform].Form!HardDisk
                strCategory = "Disks"
                strDescription = "The " & strControlName & " on Disk " & Forms![Machines]![DrivesSubform].Form!HardDisk & " was changed from " & _
                    varBefore & " to " & varAfter
        End Select
        
        DoCmd.OpenForm "ConfigChange", , , , acFormAdd
        Forms![ConfigChange].MachineID.Value = Forms![Machines].MachineID
        Forms![ConfigChange].UserID.Value = Environ("Username")
        Forms![ConfigChange].ChangeDate.Value = Now()
        Forms![ConfigChange].txtInfo.Value = strInfo
        Forms![ConfigChange].txtPrev.Value = varBefore
        Forms![ConfigChange].txtNew.Value = varAfter
        Forms![ConfigChange].Description = strDescription
        Forms![ConfigChange].Category = strCategory
 
        While SysCmd(acSysCmdGetObjectState, acForm, "ConfigChange") = acObjStateOpen
            DoEvents 'Do Nothing Wait for Closing
        Wend
      End If
    End If
    End With
  Next
  Set ctl = Nothing
  Exit Sub
 
ErrHandler:
  MsgBox Err.Description & vbNewLine _
   & Err.Number, vbOKOnly, "Error"
End Sub
 
Private Sub cmdUpdate_Click()
On Error GoTo Err_cmdUpdate_Click
 
 
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    DoCmd.Close
    
Exit_cmdUpdate_Click:
    Exit Sub
 
Err_cmdUpdate_Click:
    MsgBox Err.Description
    Resume Exit_cmdUpdate_Click
    
End Sub
Private Sub cmdCancel_Click()
On Error GoTo Err_cmdCancel_Click
 
    Dim ctlUndo As String
    
    Me.Undo
    'ctlUndo = "[Machines]![NetCardsSubform].Form!" & strActControlName
    Forms![Machines]![NetCardsSubform].SetFocus
    Forms![Machines]![NetCardsSubform].Form!IP.Undo
    Forms![ConfigChange].SetFocus
    DoCmd.Close
 
Exit_cmdCancel_Click:
    Exit Sub
 
Err_cmdCancel_Click:
    MsgBox Err.Description
    Resume Exit_cmdCancel_Click
    
End Sub

Open in new window

0
Comment
Question by:Harkins
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
15 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 24320170
When you move from a mainform to a subform, Access saves the mainform record, therefore you cannot "undo" those changes unless you're working with temporary tables. Once the changes are committed to the database, you can no longer undo them.
0
 
LVL 4

Author Comment

by:Harkins
ID: 24325299
Hi LSM,
As the code is set to run on the BeforeUpdate event of the MailForm and Subforms, it will always bring up the pop up before any changes are saved, as that point the changes can still be undone.
I have attached a small example of what I am trying to achieve. If you change anything in the school form, or the pupils subform, a popup appears asking to OK or Cancel the changes. As it stands at the moment, it will only undo all changes made to the School form, even if you have OK'd a previous field, and it will also not change anything on the Pupils subform.
What I would like it to do it only undo the field the Audit popup is asking for. I know you can undo a field by using something like...
Me!SchoolName.Undo
but as I don't the name of the field being changed I can not hard code that in, therefore I am looking to use a variable, so something like...
strChangedField.Undo
Thanks in advance for any advice for can give!
Regards,
Mark

example.mdb
0
 
LVL 85
ID: 24325992
The changes can be undone FOR EACH FORM, but you cannot undo the main form changes from the Subform, if that's what you're wanting to do. Once you move to the subform, Access has already saved the mainform data. If you move from subform to subform, Access saves the changes for all of them. So you can undo changes for the CURRENT FORM, but not for others.

You can undo the Active control:

Me.ActiveControl.Undo

If your "audit popup" is intelligent enough, it could also supply you with the name of the control that's causing the troubles. If so, you can use this syntax:

Me.Controls("NameOfYourControl").Undo

Sorry, but I don't download items from here ... I just recently finished cleaning up a nasty virus that I'm pretty sure came from a downloaded database here from EE, and I really don't want to repeat that experience.

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Author Comment

by:Harkins
ID: 24327033
I am not trying to undo changes to the main form from the subform, I am trying to undo the changes from each form in turn, so, if a user changes a value on a mainform, the popup will appear as soon as they try and click outside of the main form. The same applies to the subform, should a user change a value, the popup will appear as soon as they try and click outside of the record.
This means that the popup box is running on the BeforeUpdate of every form and subform, and that any changes are not committed until the popup form is closed. I did not mention before but the BeforeUpdate event is actually calling a procedure stored in a module.
Once the popup appears, is records the form/subform name and the field being changed, so essentially I have two variables containing that information. If I could combine that information I in theory should be able to build the exact location of the control and run the undo against it.
I appreciate you do not wish to download the DB so I have added all the code on in the example DB below. The tables and forms on the database are as below....
3 Tables
Table: Schools                   Table: Pupils                    Table: Audit
SchoolID - Auto                  PupilID - Auto                    AuditID - Auto          
SchoolName - Text           SchoolID - Num                AuditDate - Date/Time
City - Text                             PupilName - Text             SourceForm - Text
                                              PupilAge - Num                SourceField - Text
                                                                                           BeforeVal - Text
                                                                                           AfterVal - Text
Relationship is Schools:SchoolID > Pupils:SchoolID
Forms:- There is a SCHOOLS mainform with a PUPILS subform and an AUDIT form with an OK and Cancel button on.
Modules:- There is a module called basAudit which contains the AuditTrailMod code below.
Many thanks again for your help,
Mark

This is the BeforeUpdate event for the MainForm and SubForm...
 
--------------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
    'This is for the Pupils Subform
    Call AuditTrailMod(Me, PupilID)
End Sub
--------------------------------------------------------------
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
    'This is for the Schools Mainform
    Call AuditTrailMod(Me, SchoolID)
End Sub
--------------------------------------------------------------
 
This is AuditTrailMod that is stored in a Module...
 
--------------------------------------------------------------
 
Public bolUndo As Boolean
 
 
Sub AuditTrailMod(frm As Form, RecordID As Control)
 
 
  'Track changes to data.
  'recordid identifies the pk field's corresponding
  'control in frm, in order to id record.
  Dim ctl As Control
  Dim varBefore As Variant
  Dim varAfter As Variant
  Dim strControlName As String
  
  On Error GoTo ErrHandler
  'Get changed values.
  For Each ctl In frm.Controls
    With ctl
    'Avoid labels and other controls with Value property.
    If .ControlType = acTextBox Or .ControlType = acComboBox Then
      If .Value <> .OldValue Then
        varBefore = .OldValue
        varAfter = .Value
        
        bolUndo = False
        strControlName = .Name
        
        DoCmd.OpenForm "Audit", , , , acFormAdd
        Forms![Audit].SourceForm = frm.Name
        Forms![Audit].SourceField = strControlName
        Forms![Audit].AuditDate.Value = Now()
        Forms![Audit].BeforeVal.Value = varBefore
        Forms![Audit].AfterVal.Value = varAfter
 
        While SysCmd(acSysCmdGetObjectState, acForm, "Audit") = acObjStateOpen
            DoEvents 'Do Nothing Wait for Closing
        Wend
        
        If bolUndo = True Then
            Forms![Schools].Undo  'This will undo any changes made to the school form
                                  'I would like this to only undo the field that was
                                  'being cancelled on the Audit form.
        End If
        
      End If
    End If
    End With
  Next
  Set ctl = Nothing
  Exit Sub
 
ErrHandler:
  MsgBox Err.Description & vbNewLine _
   & Err.Number, vbOKOnly, "Error"
   
End Sub
--------------------------------------------------------------
 
This is the OK and Cancel button on the Audit form...
 
--------------------------------------------------------------
 
Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click
 
 
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    DoCmd.Close
 
Exit_cmdOK_Click:
    Exit Sub
 
Err_cmdOK_Click:
    MsgBox Err.Description
    Resume Exit_cmdOK_Click
    
End Sub
Private Sub cmdCancel_Click()
On Error GoTo Err_cmdCancel_Click
 
 
    DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
    
        
    'At this point I would like to be able to undo the change to the field.
    'The name of the field and the form it is on has already been collected
    'the Audit procedure, so I would like to be able to do something like...
    
    'Dim strChangedField As String
    'strChangedField = "Forms.[" & frm.Name & "]." & strControlName
    'strChangedField.undo
    
    'The following works (it may not be the prettiest) and will undo any
    'changes made to the parent form, but not subform.
    
    bolUndo = True
    
    DoCmd.Close
 
Exit_cmdCancel_Click:
    Exit Sub
 
Err_cmdCancel_Click:
    MsgBox Err.Description
    Resume Exit_cmdCancel_Click
    
End Sub

Open in new window

0
 
LVL 85
ID: 24394041
I downloaded your database. When I run the Schools form, I make a change to one of the fields in the main form (change Coventry to Conventyss). Your popup form shows, and I click Cancel. Access does NOT save this value, and reverts the value to Coventry, as expected. If I click OK, then Access DOES save the value, as expected.

The same thing happens with the subform. If I change the Name or Age, a popup appears when I move off the subform. If I click OK, the data is saved. If I click Cancel, the data isn't saved, and Access reverts to the original value.

Your data is also being saved in the Audit table. When I make a change, and select OK, a new record is inserted into the Audit table, with the appropriate data.

IOW: It appears that your form is working correctly, unless I misunderstand what you're trying to achieve. If you aren't exhibiting this sort of behavior, it could be due to (a) a faulty or corrupt database or (b) a bad install of Access. If (a), the try a Compact and REpair. If (b) then try to repair/reinstall Access.

0
 
LVL 4

Author Comment

by:Harkins
ID: 24394511
Hi LSM,
Thanks for looking into this again. Your results are very interesting, I get the main form working as you describe, but I can not get the subform to revert back. I will try Compact and Repair the DB and will also try it on another machine to see if I get the results you are seeing.
One thing I would like to improve though is that if a user makes multiple changes to the school, such as Name and Location, can the cancel button only undo the change to that specific field rather than the whole record?
I will update this with my findings as soon as I have the results!
Regards,
Mark
0
 
LVL 85
ID: 24394764
How would the cancel form know which of the fields to undo? That is, if I change Name and Location, but only want to undo the Location changes - how will your form know that is my intent?
0
 
LVL 4

Author Comment

by:Harkins
ID: 24396673
The way the audit form works is it loops through all the controls on the form to see if they have changed, therefore when the audit form appears asking to confirm/cancel the changes, it has pulled the name of the control from the schools form into the audit form (there is a field on the audit form displaying the name of the control.
Based on that, I was hoping I would be able to take the name of the control, place it into a variable and do something like:-
srtControlName.Undo
when the user clicks the cancel button. It would then continue the loop and move onto the next control. Any thoughts?
Thanks,
Mark
0
 
LVL 3

Assisted Solution

by:DK_User
DK_User earned 100 total points
ID: 24401257
Try out the Dirty property of the form. I think it could do the trick for u.

And little comment:-)
As a user i would go nuts if i had to accept every change i made in every field, and soon i would simply click Yes to everything without ever reading it. You should trust your users to be able to do things right.
Having said that, a change log is a super thing, but dont bother the user with it, unless something goes wrong.

Make a changelog so that u can revert changes if it ever becomes nescessary


Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.Dirty Then
        If MsgBox("Data has changed. Apply changes?", vbQuestion + vbYesNo) = vbNo Then
            Cancel = -1
            Me.Undo ' Thats all changes to the record. 
        Else
            makeLogRecord
        End If
    End If
End Sub
 
 
Private Sub makeLogRecord()
  Dim ctl As Control
  Dim varBefore As Variant
  Dim varAfter As Variant
  
  For Each ctl In Me.Controls
    With ctl
 
    If .ControlType = acTextBox Or .ControlType = acComboBox Then
      If .Value <> .OldValue Then
        varBefore = .OldValue
        varAfter = .Value
        
        If Not varBefore = varAfter Then
            Debug.Print varBefore & " " & varAfter
        'Just some dummycode
        'Insert into tblLog Values(School data canged,ctl.Name, varbefore, varafter, date, username)
        End If
        
        
      End If
    End If
    End With
  Next
  Set ctl = Nothing
End Sub

Open in new window

0
 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 150 total points
ID: 24401743
There are several ways to handle this.

First the simple approach. Make a copy of the current record of the subform - to a temp table, a recordset, a collection an array, whatever that fits you. Then, if the use confirms, do nothing more - if the user cancels, copy back the copy of the record.

Second, use transactions as explained once by Jim Dettman:
<quote>
Transactions allow you to buffer changes to tables and it's only done when you tell it to.  You start off with:

.BeginTrans
' allow some stuff to happen
.Commit ' Apply changes
or
.Rollback

Until A2K, you could not bind a form to a recordset, so it was not possible to use transactions with a form.  However now you can.  Open the recordset in code in the form OnOpen event and set the forms controlsource to it.

When the user clicks "Edit", issue a begintrans.  If they click "save" issue a .Commit, and if "Cancel", a .Rollback

<<What would be the difference from transactions & temp tables?>>

Less work for you and less overhead.  There are also some sticky issues like what happens when two users try to edit the same record?  Your not holding a lock on the original record anymore! So this is something you need to force if your in a multi-user situation.

Transactions wrap everything up in a nice neat bundle.
</quote>

Finally, a work-around as describe by Ken Ismert:
<quote>
.. you can't undo a subform from the parent using an Access control. But... you can trick Access Forms by adding a Microsoft Common Controls Toolbar to your form. When you click on an ActiveX control in a form, it DOESN'T fire the usual Access events, so you can very likely get a subform Undo to work, if you place the button in a Toolbar.

Although some .. recommend against all ActiveX controls, I've used this one for years with no real issues.
</quote>

/gustav
0
 
LVL 4

Author Comment

by:Harkins
ID: 24401761
Hi DK,
Thanks for your input! I agree with you on bugging the users with every change, however that is an essential part of the database. The actual database is used to log server configuration changes, so we need to record every change to each specific piece of detail. The records will not be changing often anyway!
Thanks for the OnDirty idea though, that will save the code looping through each control every time the form is closed with no changes being made! :o)
Mark
0
 
LVL 4

Author Comment

by:Harkins
ID: 24439174
Hi,
Thanks everyone for your input! I have now fixed this by simply copying the old value back into the field should the user click on the cancel button! Sometimes the solution is just so simple.
DK: My database did work as you described yours was, but I had to refresh the data to see the data revert back!
I have split the point between everyone as you attributed towards this fix and given me other ideas too!
Thanks again,
Mark
0
 
LVL 4

Author Comment

by:Harkins
ID: 24439192
My comment about refreshing the data was aimed at LSM not DK... sorry!
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question