Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Data Change Monitoring

In 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 latter 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, Customer Telephone Number or Fax Number or some other information have been changed of customers 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; do an over all check for changes in 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 on 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 show the changes to the User for review and if the response is positive from the user then update them otherwise cancel. 

Let us 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 on 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 on one or two fields on the current record.
  7. Press Ctrl+S to update the changes.

The following message box will popup:

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 is making on the record.  MS-Access will not give any clue as what changes made and in which field those changes took place.

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

  1. On the Form_Load() Event Procedure the structure of the recordset 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.
    • 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 found not matching then we assume that the User made some changes on those fields.  The Field Name, Old Value and 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:

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:

Continued on Page 2 on Report

Normally, MS-Access Reports can run into several pages and it is a custom to give page numbers on each page along with the total number of pages as an indicator.  To prepare this a Text Box is added at 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 these kind of actions 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 these 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 group of statements or actions 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 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 information in the Page Footer of the Report with a difference.  The Report can be a single page one or can have several pages. When the Report have more than one page then on the first page footer the following label should appear: Continued on Page 2.

On 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 at 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.

  1. To try our new page labels; open one of your Reports with 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;

Share:

Product Group Sequence with Auto Numbers

How to generate automatic sequence numbers for different Category of Products. 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.  Third column represents the Category-wise Product Sequence Numbers and the last column value is Product Description.  Product serial is consecutive numbers. Both Category Code combined with the Product Sequence number forms the PrimaryKey of the Table. Duplicates in Product Sequence number is 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 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, calculate the next sequence number and insert it in the ProdSeq field:

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 change in the criteria part of the DMax() Function as given below:

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

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

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

Featured Post

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...

Labels

Blog Archive

Recent Posts