Data Change Monitoring

Introduction

In a secure environment—databases implemented with Microsoft Access Security—several options are available to protect data from unauthorized changes. With User-level security, you can clearly define who can modify data, view data, or which group of users is restricted from opening a particular form. Unfortunately, these features are not available in Microsoft Access 2007 and later versions, which is surprising given their usefulness.

Regardless of whether a database is secured or not, data integrity remains crucial. Let us highlight a small issue that can arise during data editing or viewing.

Suppose a user needs to search for certain records on a form and update specific field values—for example, a customer’s telephone number, fax number, or other details. When the user moves from one record to another, Access should ideally prompt for confirmation if any changes were made to the record before saving.

There are two common approaches to handling this:

1. Overall Record Check (Simple Method):
   This method checks the Dirty status of the form to determine if any changes were made to the current record. It does not detect which specific fields were modified, only that changes exist. The system can then prompt the user with a warning: if the user agrees, the changes are saved; otherwise, the update is canceled.

2. Field-Level Change Tracking (Advanced Method):
   This method tracks changes made to each individual field in the record. Before updating, it presents the modifications to the user. The record is updated only if the user confirms; otherwise, the changes are canceled. This method provides more granular control and is highly effective in maintaining data accuracy.

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:

Right-click to open Large Image in New Tab/Window.

If the user selects Yes, the changes are saved to the record. If the user selects No, the old values are restored.

In this method, the user must be vigilant about the changes they make to the record. Microsoft Access does not provide any indication of what was changed or which specific fields were modified.

A Different Approach.

The second method tracks changes in each field and shows 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. In the Form_Current() event procedure, the current record’s field values—excluding Memo, OLE Object, Hyperlink, and Attachment fields—are loaded into the Rec() array. If the user creates a new record, these values are not loaded or checked. After this step, the user may make changes to the record.

3. In the Before_Update() event procedure, the current record’s field values are loaded into a second array Rec2() and compared with the values stored earlier in the Rec() array. If any field values differ, it is assumed that the user has made changes to those fields. The Field Name, Old Value, and New Value for each changed field are formatted into a message and displayed to the user. A sample image of this message is shown below:

   

   Right-click to open Large Image in New Tab/Window.

4. At this point, the user can review the changes and reconfirm them before updating the record by selecting Yes in the message box, or choose No to cancel the changes and restore the original 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

Continued on Page 2 on Report

Introduction

Normally, MS-Access reports can span several pages, and it is often useful to display the current page number along with the total number of pages. To achieve this, a TextBox is added to the Page Footer section of the report. In the Control Source property of this TextBox, you can enter an expression like the following:

=”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 often find ourselves repeatedly writing these expressions whenever a new report is designed. If you are a VBA enthusiast, you can automate this process by creating small, reusable User-Defined Functions (UDFs) in your application. By calling the function from a Text Box’s Control Source property, you can quickly insert the required information into your report. I have created two such functions for this purpose—if you’d like to take a look at them, click here.

This approach is especially efficient for developers aiming to standardize functionality across multiple Access applications, much like the concept discussed in your blog post on the MS-Access Reference Library.

Returning to the topic of the page indicator, we aim to display page continuity information in the Report’s Page Footer, but with a slight variation. A report may consist of a single page or multiple pages. When the report spans more than one page, the footer on the first page should display: “Continued on Page 2.”

On subsequent pages (page 2, page 3, etc.), the label should update accordingly—for example, “Continued on Page 3”, and so on—up to the second-to-last page. This label should not appear on the final page. If the report consists of only a single page, the label should be omitted entirely.

Using the first example at the beginning of this article, a single-page report will simply 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

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

The first two columns represent the Product Category Code and its Description, respectively. The third column contains the Category-wise Product Sequence Numbers, and the fourth column lists the Product Description. The product serial number is consecutive. The combination of Category Code and Product Sequence Number forms the Primary Key of the table, so duplicate Product Sequence Numbers are not allowed.

The Product file contains multiple products under each category (e.g., 1. Beverages, 2. Condiments, etc.), and each product within a category should have its own unique sequence number. Product sequence numbers must not contain duplicates.

When a new product is added to a category, the form should automatically generate the next sequence number. Manually tracking the last sequence number used for each category during data entry is impractical. However, MS Access can handle this task efficiently and accurately. A simple VBA routine on the form can accomplish this automatically.

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

Right-click to open Large Image in New Tab/Window.

The first field, Category Code (1, 2, 3, etc.), is a lookup field implemented as a combo box linked to the Category Table. On the property sheet, the first column width is set to 0 so that the category description is displayed in the combo box instead of the numeric code.

During data entry, the user first selects a Product Category in the initial field to prepare for adding a new product under that category. When the user presses the Tab key to move out of the Category field, the next sequence number (i.e., the existing Product Sequence Number + 1) is automatically inserted into the ProdSeq field. The user then needs to enter only the Product Description manually. The ProdSeq field can be locked to prevent accidental changes.

The following program runs in the Category_LostFocus() event procedure. It identifies the highest existing Product Sequence Number for the selected category, calculates the next sequence number, and automatically inserts it into 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 a 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