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

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
avalonwgiAuthor Commented:
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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

avalonwgiAuthor Commented:
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
Rey Obrero (Capricorn1)Commented:
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..
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

avalonwgiAuthor Commented:
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.
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.

avalonwgiAuthor Commented:
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
avalonwgiAuthor Commented:
Can anyone help me on this please?
Do you have an ID column in you table?
avalonwgiAuthor Commented:
Yes.  In all tables
So the example will work for you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.