Solved

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
13
239 Views
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
code.txt
0
Comment
Question by:avalonwgi
  • 6
  • 5
  • 2
13 Comments
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 240 total points
ID: 22782884
0
 
LVL 18

Accepted Solution

by:
jmoss111 earned 260 total points
ID: 22782888
Hi avalonwgi,

The attached might give you some ideas

Regards,

Jim
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

0
 

Author Comment

by:avalonwgi
ID: 22782924
Capricorn,
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.
0
 

Author Comment

by:avalonwgi
ID: 22782941
JMoss,
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
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22782958
avalonwgi,
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..
0
 
LVL 18

Expert Comment

by:jmoss111
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.

Jim
Private Sub Form_BeforeUpdate(Cancel As Integer)

  Call AuditTrail(Me, RecordID) ' Gets the unique id of the record

End Sub

Open in new window

0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:avalonwgi
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.
0
 
LVL 18

Expert Comment

by:jmoss111
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.

Jim
0
 

Author Comment

by:avalonwgi
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
0
 

Author Comment

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

Expert Comment

by:jmoss111
ID: 22788510
Do you have an ID column in you table?
0
 

Author Comment

by:avalonwgi
ID: 22788746
Yes.  In all tables
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22791618
So the example will work for you
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now