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.

• Create Menus with Macros
• Join Split Array Function-2
• Join Split Array Function
• Memo Field and Data Filtering
• Lively Controls on Form