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

Read More

Continued on Page 2 on Report

Introduction

Normally, MS-Access Reports can run into several pages and normally we give page numbers on each page along with the total number of pages as an indicator.  To do this,  a TextBox is added to the  Page Footer of the Report and an expression like the following is entered into the Control Source property:

=”Page “ & [page] & “ of “ & [pages]

Result: Page 1 of 15

OR

=”Page: “ & [page] & “ / “ & [pages]

Result: Page: 1 / 15

Report Date is also added in the page footer area like =”Date: “ & format(date(),”dd/mm/yyyy”)

We keep writing these expressions repeatedly every time when a new report is designed.  If you are a VBA enthusiast and using VBA routines in your applications, then this kind of action can be automated with the use of User-Defined Functions and call the function from the Text Box's Control Source property to quickly insert this information on the Report.  I have written two functions for this purpose and if you are interested to take a look at them then click here.

When you are working with VBA routines in Standard Modules or Class Modules you can see that certain groups of statements or actions are repeated at different places, which are useful across different Applications.  They can be customized and written in the form of public functions and added to your User-Defined Function Library so that they can be called with a one-line statement.  The lengthy VBA Routines can be compressed this way.  There is a Blog Post related to this topic: MS-Access and Reference Library.

Coming back to the Page indicator topic we will try to display the page continuity information in the Page Footer of the Report with a difference.  The Report can be a single page or can have several pages. When the Report has more than one page, then on the first-page footer the following label should appear: Continued on Page 2.

On the next page (i.e., on page 2) Continued on Page 3 and so on till the last but one page.  This label should not appear on the last page.  If the report has only one page then this label should not appear at all.

With the first example given at the top of this Article, the Report with only one page will print as Page 1 of 1.

Try out the Page Footer Setting

1. To try our new page labels; open one of your Reports with a few pages.

2. Create a Text Box, wide enough to display the label; like Continued on Page 99; at the Page Footer of the Report.

3. Write the following expression in the Control Source Property of the Text Box.

4. =IIf([Pages]>1 And [Page]<[Pages],"Continued on Page " & [Page]+1,"")

5. Save the Report and open it in Print Preview.

6. Check the last page of the Report.  This label should not appear there.

7. Try this out on a single-page Report.

NB: [page], [pages] are System Variables and they should be used in the expression without change. &hypen;

Earlier Post Link References:

• Join Split Array Function-2
• Join Split Array Function
• Memo Field and Data Filtering
• Lively Controls on Form
• Create your own Color Palette

Read More

Product Group Sequence with Auto Numbers

Introduction.

How to generate automatic sequence numbers for different Categories of Products in a Form.  See the sample data given below to understand the gravity of the problem more clearly:

CatCod	Category	ProdSeq	Product Description
1	Beverages	1	Coffee
1	Beverages	2	Tea
1	Beverages	3	Pepsi
2	Condiments	1	Aniseed Syrup
2	Condiments	2	Northwoods Cranberry Sauce
2	Condiments	3	Genen Shouyu
2	Condiments	4	Vegie-spread
3	Confections	1	Uncle Bob's Organic Dried Pears
3	Confections	2	Tofu
1	Beverages	4
2	Condiments	5

First, two-column values are Product Category Code and Description respectively.  The third column represents the Category-wise Product Sequence Numbers and the last column value is the Product Description.  The product serial is consecutive numbers. Both Category Code combined with the Product Sequence number forms the Primary Key of the Table. Duplicates in the Product Sequence number are not valid.

The Product file contains several products under each category (1. Beverages, 2. Condiments, etc.), and the products under each group should have their own sequence numbers.  The Product sequence should not end up with duplicate values.

When a new product is added to the file under a particular Category, the Form should generate the next product sequence number automatically.  It is impossible to keep track of the last sequence number used under each product group manually, during data entry.  But we can give that job to MS-Access to do it effectively without mistakes.  A simple VBA routine on the Form can do it for us.

An image of a sample Form for the above data is given below:

The first field is the Category Code (1,2,3 etc.) is a lookup field (Combo box) linked to the Category Table.  The first Column Width is set to 0 on the Property Sheet so that the description of the Category is displayed in the Combobox rather than the numeric value.

During data entry, the user selects a Product Category on the first field in preparation for entering a new Product under that Category.  The next sequence number (the existing Product sequence number + 1) inserted automatically in the second field ProdSeq when the User presses Tab Key to leave out the Category field. The User needs to enter only the Product Description manually.  The ProdSeq field may be kept locked so that it is protected from inadvertent changes.

The following program runs on the Category_LostFocus() Event Procedure and finds the existing highest Product Sequence Number for the selected Category from the Table, calculates the next sequence number, and inserts it in the ProdSeq field:

The Category LostFocus Code.

Private Sub Category_LostFocus()
Dim i, s
If Me.NewRecord Then
     i = Me!Category
     s = Nz(DMax("ProdSeq", "Products", "Category = " & i), 0)
     s = s + 1
     Me![ProdSeq] = s
End If 
End Sub

The program works only when the user attempts to create a New Record.

If the Category Code is Text value, then you need to make a change in the criteria part of the DMax() Function as given below:

s = Nz(DMax("ProdSeq", "Products", "Category = ‘" & i & "’"), 0)

Technorati Tags: Microsoft

• Join Split Array Function
• Memo Field and Data Filtering
• Lively Controls on Form
• Create your own Color Palette
• Colors 24 bits and Binary Conversion

Read More