Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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
Medium Priority
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 720 total points
ID: 22782884
LVL 18

Accepted Solution

jmoss111 earned 780 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.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


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:

http://articles.techrepublic.com.com/5100-10878_11-6166807.html 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

581 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