Audit Trail VBA fine tuning: before/after list of changes (1 Viewer)

[EdK]

Hi all


I am writing a book using Access 2007. It includes a form/table which holds several memo fields. I have been keeping track of changes made to any/all of these memo fields. I have used the following VBA routine -

Function AuditTrail()
On Error GoTo Err_Handler

Dim MyForm As Form, C As Control, xName As String
Set MyForm = Screen.ActiveForm

'Set date and current user if form has been updated.
MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
"Changes made on " & Date & " by " & CurrentUser() & ";"

'If new record, record it in audit trail and exit sub.
If MyForm.NewRecord = True Then
MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
"New Record """
End If

'Check each data entry control for change and record
'old value of Control.
For Each C In MyForm.Controls

'Only check data entry type controls.
Select Case C.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup
' Skip Updates field.
If C.Name <> "Updates" Then

' If control was previously Null, record "previous
' value was blank."
If IsNull(C.OldValue) Or C.OldValue = "" Then
MyForm!Updates = MyForm!Updates & Chr(13) & _
Chr(10) & C.Name & "--previous value was blank"

' If control had previous value, record previous value.
ElseIf IIf(IsNull(C.Value), "", C.Value) <> C.OldValue Then
MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
C.Name & "==previous value was " & C.OldValue
End If
End If
End Select
Next C

TryNextC:
Exit Function

Err_Handler:
If Err.Number <> 64535 Then
MsgBox "Error #: " & Err.Number & vbCrLf & "Description: " & Err.Description
End If
Resume TryNextC
End Function

My problem: It does a great job (as far as it goes) in putting into an audit field control, details of "before" and "after" memo records. But it is a real pain, trying to compare the "before" and "after" memo record(s) when, as is mostly the case, the size of the "before" memo field and the "after" memo field can exceed one A4 page, even though (as is often the case) only perhaps one word, or one line is different between the two.

What I would like to have: (simply) just a list of a) words that were in the "before" memo field that are not in the "after" memo field b) vice versa - ie words that were NOT in the "before" memo field but ARE in the "after" memo field. [preferably with headings, in upper case: ie DELETIONS and ADDITIONS

sounds easy?

I am not even a beginner at VBA. However, I sort of can see that possibly (probably?) the present Audit trail VBA routine (above) would have to be changed to create "before" and "after" tables, then a code to say "compare the 'before and after' records to find [as per a)( and b), above] and list the results to two new fields called "additions" and "deletions" - am I on the right track?

BTW the relevant fields within the "Booktext" table are: ID (auto generated); booktext1; booktext2; booktext3; booktext4; and updates.

I'm afraid I would have to be given the complete VBA code to be able to paste it into my database, as I lack the capacity to convert from general/broad suggestions into actual working VBA.

I know someone will query 1) why I don't use MS Word or Publisher to write a book 2) why I don't use just one memo field instead of four. I have pretty good reasons (I think), aspecially re 2), that is because I need flexibility in inserting images at various optional but set spots on a page (it works really well, BTW). Re 1), it is far too late for me to back track and do the book in MS Word or Publisher. IMHO MS Word is very tricky with the inserting of images with text, and quite flukey with conversion to eBook format(s), though opinions do differ on that ....

Hoping you can help me and/or at least find the above exercise interesting ...

EdK 29 July 2015

 

[spikepl]

So:

"a"- deleted 5, added 7
"the" - deleted 9, added 5
"is" - deleted 10, added 2
...


something like that? It really doesn't sound very useful. And which you cannot even do by inspecting the end result and comparing with the initial text, because you can only count the difference in occurrences, not how it came about (unless you write a text processor).

 

[EdK]

Thanks for your comments, Spike. Yes, I agree, that (of itself) wouldn't be very useful. But I should have said a couple more things about how the database/book text is designed and how I use it -- The three fields booktext1, booktext2, and booktext3 are in a table that is organised by ID plus paranumber so the Audit report I have at present (and will have with any redesigned Audit trail process) is narrowed down to a range of paragraph numbers, using in its query the routine "between [input 1st para number] and [input 2nd para number]; in fact, if I want, it can be just one paragraph number. The other thing is that I periodically print off a copy of the book section(s) I am working on, which mostly equates to the baseline data for "previous" and from which (as needed) I can (or I think I should be able to) quickly and easily reconstruct what I changed. And of course, as long as I do have a reasonably recent print of the particular paragraph, I reckon it should be OK. Anything would be better than what the audit report gives me now, it contains too much irrelevant information and forces me to wade through too much material and my poor brain can't cope.

The main problem with the Audit Trail VBA routine above is that it puts into the Updates field the whole of the "before" content, even if just one word was changed, and it doesn't highlight what that word was - so you see my problem ... Almost a "needle in haystack" job.

Hope this makes a bit of sense. I agree, of itself, the report as you neatly show is pretty useless. The other thing I should say is that if it is just one word that was changes, I'm not interested in it, really , and I would skip over it in an instant, I would be looking for chunks of words eg sentence(s), paragraphs, and possibly even pages.

What I would probably do is print off (say) each week a report for whatever part (or parts) of the book I am working on, or even for the whole book, and then file it for reference as/if needed, probably not even really looking at it at the time. Then I would purge the contents of the Updates field to reduce the size of the database, as that field very quickly becomes bloated. Being able to refer to such reports at later times would greatly assist with the self-editing process, help to reduce the amount of printed stuff I have lying around.

So, putting aside the usefulness or otherwise of this for me, can you or some clever person suggest the code to use to make it work so that I can try it for usefulness ?

Thanks EdK

 

[CJ_London]

Perhaps a halfway house is rather than storing the changes to have a vba routine which compares the 'before and after'.

e.g. split each text into an array on a space to get individual words, then compare element by element until a change is found (or by character by character) - this a least tells where the later text diverges from the earlier text.

using vbbinarycompare will enable you to see if you have changed case if that is a required change notification.

Haven't tried it, but have you considered using word with track changes enabled and then automating an update into Access

 

[EdK]

Thank you, CJ! very interesting, those bits I could understand. I posted a bit more explanation of what I had in mind and how I might use a new routine.

I can now cut to the chase, having thought a bit more about it.

The only change to the VBA code that I posted earlier, I think (not being even a novice) is (most of the way through the code):

Where it says 'If control had previous value, record previous value' - change it to read 'If control had previous value, record new value' - this is because I don't really want to know the previous memo field value, I can see that in my book paragraph printouts.Where it says, a bit lower down 'C.name & "previous value was " & C.OldValue', change that to 'C.name & "new value is " & C.NewValue'

you get the idea, I am only interested in a) the fact that there is a new value b) what that value is

Can somebody make the VBA work to this direction, plse?

EdK

 

[spikepl]

You really really need to think about this, before asking people for anything. You seem to think that coding can perform mircales - it cannot.

CJ-London's array idea is a complete non-starter. Insert an "a" and the whole lot shifts one word and you get diffs all over.

This is not a simple thing. Play with it on paper -compose a sentence or two with words on Post-Its, and realize how complex the task is, even if only comparing before and after. But keeping track of changes as they happen (insert/delete/change letetrs or words or entire sentences) is also complex - don't count on anyone making VBA code for you.

USe what there is, as CJ-London also suggested : track changes in Word.

 

[spikepl]

On reflection: free utilities exist to compare lines in code, so when you have a file full of computer code the application lists old and new file and shows you which lines differ. This is aimed at programming, but might be usable as a minimalistic tool. Google "file diff" and test a tool or two.

 

[CJ_London]

CJ-London's array idea is a complete non-starter. Insert an "a" and the whole lot shifts one word and you get diffs all over.

I said

this a least tells where the later text diverges from the earlier text.

which gives you a start point. I wasn't suggesting that once a difference is found, the routine continues because as you say, once a word is added or removed, all further comparisons are pointless.

 

[EdK]

Thanks to all for your responses, and my apologies - I didn't mean to stir the pot (so to speak). I will try out the two suggestions (using MS Word's tracking routine, which I didn't know about but should have; using some sort of utility, to be found by Googling as Spikepl suggests). I'll see how easy it is to get from MS Access ----> Word (?via Excel). Thanks again EdK