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
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