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
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
13 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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 120

Expert Comment

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

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

732 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