Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Data Change Monitoring

Introduction

In a secured environment (i.e. Databases implemented with Microsoft Access Security) several options are available to protect data from unauthorized changes by Users.  With User-level security settings, we can define clearly who can make changes to the data view data or which group of Users are not allowed to open a Form, and so on.   But, these features are not available from Microsoft Access 2007 and later versions.  I wonder why Microsoft discarded this powerful feature now.

Whether the database is secured or not data integrity is an important factor; no matter what method we adopt to protect it.  We are going to highlight a small issue that can happen during data editing/viewing. 

Assume that the user has to search and find certain records on the Form and modify certain field values.  For example, a Customer's Telephone Number or Fax Number or some other information has been changed and needs to update those records on the file.  When the user moves out from one record to the other, if the user has made any changes to the record, then MS-Access should ask for reconfirmation from the User before updating the record.

1.  The first method is very simple; does an overall check for changes in the current record, irrespective of which field the user has changed. This can be done by simply checking the Dirty Status of the Form.  But, this method cannot detect which field the user has changed and which one is not.  We can only give a warning to the User that he has made some changes to the record.  If the user agrees to update the changes, then proceed otherwise cancel.

2.  The second method is very effective and tracks each field change the User makes on the record.  Before updating the record shows the changes to the User, and if the response is positive from the user then update them otherwise cancel. 

Try the first method on a form.

  1. Open one of your databases with a Data Editing Form.

  2. Open an existing Form in Design View.

  3. Display the VBA Module of the Form.

  4. Copy and Paste the following code into the VBA Module of the Form (you can use this code in any form):

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim msg As String
    
    On Error GoTo Form_BeforeUpdate_Err
    
      If Me.Dirty And Not Me.NewRecord Then
         msg = "Update the changes on Record." & vbCr & vbCr & "Proceed...?"
         If MsgBox(msg, vbYesNo + vbDefaultButton2 + vbQuestion, "Updating Changes") = vbNo Then
             Me.Undo
         End If
      End If
    
    Form_BeforeUpdate_Exit:
    Exit Sub
    
    Form_BeforeUpdate_Err:
    MsgBox Err & " : " & Err.Description, , "Form_BeforeUpdate()"
    Resume Form_BeforeUpdate_Exit:
    End Sub
  5. Save the form and open it in Normal View.

  6. Make some changes to one or two fields of the current record.

  7. Press Ctrl+S to update the changes.

The following message box will pop up:

If the user selects Yes the changes will be updated on the record otherwise old values will be restored.  In this method, the User must be vigilant about the changes that he/she makes to the record.  MS-Access will not give any clue as to what changes were made and in which field those changes took place.

A Different Approach.

The second method tracks change in each field and show them to the user.  The following steps we have followed to implement this method:

  1. In the Form_Load() Event Procedure the structure of the record set attached to the Form is scanned for Field Names and Data Types.

    • Field Names and Data Types are loaded into two similar Variant Arrays (Rec() and Rec2(), both are two-dimensional arrays), leaving one element of the Array for loading Field Values later.

    • The memo, OLE Object, Hyperlinks, and Attachment fields are exempted from validation checks.

  2. On the Form_Current() Event Procedure the current record field values (except Memo, OLE Object, Hyperlinks, and Attachment fields) are loaded into Rec() Array.  If the user creates a new record, then the values are not loaded or checked.  After this action, the User may make changes to the record.

  3. On the Before_Update() Event Procedure the current record values are loaded into the second Array (Rec2()) and compared with the values loaded into Rec() Array earlier.  If any of the field values are found not matching then we assume that the User made some changes in those fields.  The Field Name, Old Value, and the New Value of those fields will be formatted into the form of a message and displayed to the User. See the sample image given below:

  4. Here, the user can check his changes and reconfirm before updating the record by selecting Yes from the Message Box or No to cancel the changes and restore old values.

You may copy and paste the following code into the VBA Module of any data editing Form and try it out as we did earlier:

The Form's Class Module VBA Code.

Option Compare Database
Option Explicit

Dim Rec() As Variant, Rec2() As Variant, j As Integer
Dim rst As Recordset, fld_count As Integer, i As Integer


Private Sub Form_BeforeUpdate(Cancel As Integer)
'-----------------------------------------------------
'Author : a.p.r.pillai
'Date   : May 2011
'Remarks: All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------
Dim msg As String

On Error GoTo Form_BeforeUpdate_Err

If Me.Dirty And Not Me.NewRecord Then
   'Load Field Values after changes into the second array
   For i = 0 To fld_count
       If Rec(i, 0) <> "xx" Then
         Rec2(i, 1) = Me.Controls(Rec(i, 0)).Value
       End If
   Next
   
   'Identify fields with changes made
   'and Mark them.
   For i = 0 To fld_count
     If Rec(i, 0) <> "xx" Then  'If Memo/OLE Object/Hyperlink/Attachment field then skip
        If Rec(i, 1) = Rec2(i, 1) Then
           Rec2(i, 2) = False
        Else
           Rec2(i, 2) = True
        End If
     End If
   Next

   msg = ""
   'Take changed field values and format a message string
   For i = 0 To fld_count
      If Rec2(i, 2) = True And Rec(i, 0) <> "xx" Then
         msg = msg & "[" & UCase(Rec(i, 0)) & "]" & vbCr
         msg = msg & "       Old:  " & Rec(i, 1) & vbCr
         msg = msg & "      New:  " & Rec2(i, 1) & vbCr & vbCr
      End If
   Next
   'If not approved by User reverse the change.
   If Len(msg) > 0 Then
      msg = msg & vbCr & "Update the changes..?"
      If MsgBox(msg, vbYesNo + vbDefaultButton2 + vbQuestion, "Update Change") = vbNo Then
           Me.Undo
      End If
   End If
End If

Form_BeforeUpdate_Exit:
Exit Sub

Form_BeforeUpdate_Err:
MsgBox Err & " : " & Err.Description, , "Form_BeforeUpdate()"
Resume Form_BeforeUpdate_Exit

End Sub

Private Sub Form_Current()
'-----------------------------------------------------
'Author : a.p.r.pillai
'Date   : May 2011
'Remarks: All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------

On Error GoTo Form_Current_Err
'Load the current record value into array
'Before change
For i = 0 To fld_count
    If Rec(i, 0) <> "xx" Then
        Rec(i, 1) = Me.Controls(Rec(i, 0)).Value
    End If
Next

Form_Current_Exit:
Exit Sub

Form_Current_Err:
MsgBox Err & " : " & Err.Description, , "Form_Current()"
Resume Form_Current_Exit

End Sub

Private Sub Form_Load()
'-----------------------------------------------------
'Author : a.p.r.pillai
'Date   : May 2011
'Remarks: All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------

On Error GoTo Form_Load_Err
Set rst = Me.RecordsetClone
fld_count = rst.Fields.Count - 1

'Redimension the array for Number of fields
ReDim Rec(0 To fld_count, 0 To 2) As Variant
ReDim Rec2(0 To fld_count, 0 To 3) As Variant
'Load field Name and Type into array
'Memo Field type is 12 and will be excluded
'from validation checks
For i = 0 To fld_count
   j = rst.Fields(i).Type
   If j <> 11 And j <> 12 And j <> 101 Then
       Rec(i, 0) = rst.Fields(i).Name
       Rec2(i, 0) = Rec(i, 0)
       Rec2(i, 3) = rst.Fields(i).Type
   Else
       Rec(i, 0) = "xx"
   End If
Next

Form_Load_Exit:
Exit Sub

Form_Load_Err:
MsgBox Err & " : " & Err.Description, , "Form_Load()"
Resume Form_Load_Exit
End Sub

The Code is not extensively tested for logical errors. Use it at your own risk.

Share:

1 comment:

  1. Excellent article. I added few more lines to your code to insert into a Auditlog table so I can keep track of all changes. Thanks for sharing your knowledge. Helps a lot.

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code