Acess VBA code to ID changes to fields, compare data against current and then write new record to table and write a record of change to the revision table

Posted on 2008-10-22
Last Modified: 2012-05-05
I have attached my current Save code.  I am trying to figure out how to cycle through fields on a form, determine if the data was changed, compare that data against the current record in the table and then write a new record to the table with a higher revision number and also write out the actual field(s) that were changed to a revision history table.  I think I have everything else working except the modify process.  THis is extremely critical to my project and need as much help and as fast as I can get it.  Can you help?  Dave
Question by:avalonwgi
  • 6
  • 5
  • 2
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 240 total points
ID: 22782884
LVL 18

Accepted Solution

jmoss111 earned 260 total points
ID: 22782888
Hi avalonwgi,

The attached might give you some ideas


Const cDQ As String = """" 
Sub AuditTrail(frm As Form, recordid As Control)
  'Track changes to data.
  'recordid identifies the pk field's corresponding
  'control in frm, in order to id record.
  Dim ctl As Control
  Dim varBefore As Variant
  Dim varAfter As Variant
  Dim strControlName As String
  Dim strSQL As String
  On Error GoTo ErrHandler
  'Get changed values.
 For Each ctl In frm.Controls
  With ctl
    'Avoid labels and other controls with Value property.
   Select Case ctl.ControlType
     Case acTextBox, acCheckBox	
      If .Value <> .OldValue Then
        varBefore = .OldValue
        varAfter = .Value
        strControlName = .Name
        'Build INSERT INTO statement.
        strSQL = "INSERT INTO " _
           & "Audit (EditDate, User, RecordID, SourceTable, " _
           & " SourceField, BeforeValue, AfterValue) " _
           & "VALUES (Now()," _
           & cDQ & Environ("username") & cDQ & ", " _
           & cDQ & recordid.Value & cDQ & ", " _
           & cDQ & frm.RecordSource & cDQ & ", " _
           & cDQ & .Name & cDQ & ", " _
           & cDQ & varBefore & cDQ & ", " _
           & cDQ & varAfter & cDQ &")"  
        'View evaluated statement in Immediate window.
        Debug.Print strSQL
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
      End If
    Case Else
   End Select
  End With

Open in new window


Author Comment

ID: 22782924
I have seen this link several times, but it does not address my issue.  That is to identify and write record changes.  I am trying to accomplish field level changes and capture them individually.  I would appreciate it if you would read the issue completely before sending out a generic answer done by MS.
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.


Author Comment

ID: 22782941
I had started down this road, but how do you get the value of a field to compare against the current database record field?  I see you reference that value <> old value but I don't see where the old value is retrieved.  I have a sql statement that pulled the Old values.  Perhaps you can explain this better for me.  I also don't understand the constat cDQ thing at all.  Thanks, Dave
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22782958
thanks for your nice comment.
if you will look closely the codes that jmoss posted is similar to the one in the link.
and don't worry i wouldn't look any more of any question you  post..
LVL 18

Expert Comment

ID: 22782995
The code came from a solution that I helped a guy with last night. The original code came from: which has the code and a good narrative to go along with it. The code in the Tech Republic article is similar to the code in the M$ link that cap1 gave you and predates the Tech Republic article by about a year. There is another audit trail article and code on Dev Ashish's site; I prefer this one. The code below is what calls the
AuditTrail sub and where the record ID comes from.

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Call AuditTrail(Me, RecordID) ' Gets the unique id of the record
End Sub

Open in new window


Author Comment

ID: 22783054
TO JMoss and Capricorn
I am still a beginner per se and need more hand holding with some of this coding.  That is why I attached the code of what I have so far, that someone could walk me through what I need to do.  I can't interpret all this code as easily and time is the importance right now.  I will read the tech republic code and see if that explains it any better.  Thanks for your help.
LVL 18

Expert Comment

ID: 22783097
The Tech Republic article was later than M$, but it has a very good narrative to go along with the code. After you read the article and it's short, I think that you'll understand much better; it's really pretty simple. Then if you have questions, call back.

I've been doing Access for quite awhile and I'm still a beginner.

You're welcome.


Author Comment

ID: 22786199
Ok JMoss and Capricorn,
None of these links again provide me what I am looking for.  THe reason is that those require the form to be bound and all of my forms are unbound and for good reasons.  So looking at my code that I have so far, I have a way to pull up the current data through a sql stmt.  I also can list out the individual controls and then do a comparison against the old data and if it changed I guess I could individually jump to the REVISION code to record the change to revision history.  What I am looking for is a a more efficient and flexible way to write this.  Because, some forms have 20-30 fields on it.  ALso is there a way by looking at what I have in the REVISION area to see if there is a more efficient way to execute this in batch and not go one by one?  Could either of you please help on this? Dave

Author Comment

ID: 22787486
Can anyone help me on this please?
LVL 18

Expert Comment

ID: 22788510
Do you have an ID column in you table?

Author Comment

ID: 22788746
Yes.  In all tables
LVL 18

Expert Comment

ID: 22791618
So the example will work for you

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
File.Search issue 8 35
ADODB problem 20 38
record saved form with no buttons or X 3 21
Treeview control in 64 bit Office. 2 24
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question