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:
-
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. -
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.
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 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:
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.
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.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 theRec()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: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 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