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.
Open one of your databases with a Data Editing Form.
Open an existing Form in Design View.
Display the VBA Module of the Form.
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 SubSave the form and open it in Normal View.
Make some changes to one or two fields of the current record.
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:
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.
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.
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:
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 SubThe Code is not extensively tested for logical errors. Use it at your own risk.
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