Solved

Undo changes to a subform - Access 2003 VBA

Posted on 2009-05-06
15
2,760 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
15 Comments
 
LVL 84

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 84
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
 
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 84
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 84
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 49

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now