Combine Memo Field into Chronologic Sequence 1

[sterlecki]

I want to capture memofields from multiple tables and concatenate them so I can see changes chronologically for all the tables or chronologically by table when grouped by table.

Each table in my application tracks additions and changes to the data in a memo field "Updates" and each change is time stamped.
The Updates entries have this format:

[highlight] County Data Added: Sublette by 05830 on 6/16/2009 3:10:54 PM
State Data Added: Wyoming by 05830 on 6/16/2009 3:10:54 PM
RigName Data Added: Unit 235 by 05830 on 6/16/2009 3:12:06 PM
Mudloggers changed from '1-man' to '1 man- 300' above TKU to TD' by 05830 on 6/16/2009 3:31:36 PM
New Record Added by 05830 on 6/16/2009 3:31:45 PM
API Data Added: 49035271640000 by 05830 on 6/16/2009 3:31:45 PM[/highlight]

It's fairly easy to concatenate the Updates field for each of the 22 tables although sort of cumbersome. So far I've been doing the following:

Updates Summary: "  data_WellHeader  " & [data_WellHeader]![Updates] & "  data_LogEval  " & [data_LogEval]![Updates] & "  data_LogEvalDetail  " & [data_LogEvalDetail]![Updates]

I would like to be able to parse each line and be able to sort by the time stamp. I would also like be able to assign the table name to each line entry so I can sort or group by table name if needed.

[highlight]The only way I can think of doing this is to:
1. Concatenate all the Updates fields
2. Capture them in a txt file
3. Create some macro\VBA code to parse the data in Excel
4. re-import the data into access to create a report[/highlight]

The report would be for a single record and I probably would not be doing this for more than a few records at a time.

Questions:

Best Way to Concatenate?
Would a Union Query work here?
Once concatenated anyway to sort by the timestamp on that line?

Any other suggestions would be appreciated.

 

[dhookom]

Have you considered creating a more normalized audit solution? IMO, a db audit table might have a structure like:
[tt][blue]
ztblAudits
===================
audAudID autnumber primary key
audDateTime date and time of change
audTable
audField
audNewValue
audChangeType
audChangedBy
[/blue][/tt]
This would be much easier to query, aggregate, report,...

Duane
Hook'D on Access
MS Access MVP

 

[traingamer]

You can use a union query if you really must put all of the results together in one big memo field, but I would simply put them as individual entries in one table with a layout something like:
key: autonumber
wellid: OR whatever your actual identifier is (that ties them together)
timestamp: datetime - the data from the time stamp as an actual Access datetime
source: the source table or the type - tells you the type of data (state, county, API, etc.)
Memo: a memo field with just the comment stripping the date time stamp,

Then you could sort them or select them however you like without having to shoehorn them into an oversized memo field.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[traingamer]

Tthat looks pretty similar!

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[sterlecki]

Hello dhookom and traingamer,

traingamer: I suspect you must be in the oil biz also as you picked up on the WellID even though it was not specifically mentioned in my post??


As you might have suspected I have been using the AuditData() function that is often referenced on Tek-Tips threads for such purposes and triggered in the Form's BeforeUpdate Event.(see code)

In General it has been working fine and on a form to form basis a user can scroll down through the Updates field on the form and see the chronological history for this form.

What both of you have suggested seems possible if I use this code and in stead of writing to the Updates Field of the form's table that I write the changes to the suggested tblAudits.

Two questions that I have are;

How would I capture the TableName for the active Form?

I presume that I would have to create a recordset each time the code searched through the controls and then do an AddNew to the tblAudits and insert the appropriate "changetype" ie New, changed, deleted as the circumstances dictated?

Any suggestions will help.

Public Function AuditData()
'Instructions for the Audit Trail Function
'
'1. Open your Access program and add a new module.  Copy and paste the
'   function (in gray) to this module, then save and close the module.
'2. Open the table you want to audit in table design view and add a new
'   Memo field called 'Updates'.
'3. Open the form that references the table and add this field to your secured
'   form.  Set the Locked property to Yes.
'4. In the form properties window, add the following to
'   the BeforeUpdate property: =AuditData()



' Comments   : This function is used to provide an audit trail of all changes to a record
'        : entered on a secured form.  After adding a locked memo field called 'Updates'
'        : on the source form, add a reference to the Before Update event to this function.
' Requirement: A bound memo field named 'Updates' must exist on the active form
' Parameters : None
' Returns    : Appends Audit Trail data as a string to the memo field on the active form
' Created By : Ofni Systems Inc.
' Modified   : 04/07/01
' --------------------------------------------------------
Dim frmActive As Form
Dim ctlData As Control
Dim strEntry As String

On Err GoTo NextCtl

    Set frmActive = Screen.ActiveForm
    
    'When using subforms use the following statements where "Main Form" is the name of the Parent Form
    ' Added by SWT
    
    If frmActive.Name = "Main Form" Then
    Set frmActive = Screen.ActiveControl.Parent
    End If
    
    'Determine who, when and where
    'Original code
    'strEntry = " by " & Application.CurrentUser & " on " & Now
    
    'Determine who, when and where using Windows Logon
     strEntry = " by " & fOSUserName() & " on " & Now
    
    ' If new record, record it in audit trail and exit sub.
    If frmActive.NewRecord = True Then
       frmActive!Updates = frmActive!Updates & "New Record Added" & strEntry
       'this line added by SWT to get the UserCreated filled.
       frmActive!UserCreated = fOSUserName()
       frmActive!DateCreated = Now()
                    
       'Uncomment the next line if you do not want to record the details of the initial entry
       'Exit Function
    End If
    
    'Check each data entry control for change and record
    For Each ctlData In frmActive.Controls

       ' Only check data entry type controls.
       Select Case ctlData.ControlType
          Case acTextBox, acComboBox, acCheckBox, acOptionButton
             ' Skip Updates field.
            If ctlData.Name = "Updates" Then GoTo NextCtl
            
            'Added by SWT
            'keeps the DateModified and UserModified field changes from appending to "Updates"
            If ctlData.Name = "DateModified" Then GoTo NextCtl
            If ctlData.Name = "UserModified" Then GoTo NextCtl
            
            
            'Skip unbound controls (3 is ControlSource)
            If ctlData.Properties(3) = "" Then GoTo NextCtl
            
             
             Select Case IsNull(ctlData.Value)
                'Check for deleted data
                Case True
                    If Not IsNull(ctlData.OldValue) Then
                        frmActive!Updates = frmActive!Updates & Chr(13) & Chr(10) & "  " & ctlData.Name & " Data Deleted: Old value was '" & ctlData.OldValue & "'" & strEntry
                    End If
                'Check for new or changed data
                Case False
                    If IsNull(ctlData.OldValue) And Not IsNull(ctlData.Value) Then
                        frmActive!Updates = frmActive!Updates & Chr(13) & Chr(10) & "  " & ctlData.Name & " Data Added: " & ctlData.Value & strEntry
                    'If control had previous value, record previous value.
                    ElseIf ctlData.Value <> ctlData.OldValue Then
                        frmActive!Updates = frmActive!Updates & Chr(13) & Chr(10) & "  " & ctlData.Name & " changed from '" & ctlData.OldValue & "' to '" & ctlData.Value & "'" & strEntry
                    End If
                    
                   If frmActive.NewRecord = True Then
                   frmActive!UserModified = ""
                   frmActive!DateModified = ""
                   Else
                    frmActive!UserModified = fOSUserName()
                    frmActive!DateModified = Now()
                    End If
            End Select
   End Select
NextCtl:
   Next ctlData
    
  
End Function

 

[dhookom]

I would find a good solution that has already been created such as Allen Browne's Creating an Audit Log

Duane
Hook'D on Access
MS Access MVP

 

[sterlecki]

Thanks dhookom for that link. I will try it.

 

[sterlecki]

I had success modifying the code that I had been using as posted above. The results seem to work fine so far.

Basically it entails creating another table as dhookom suggests. I created the following:

Create a table named audit_Changes and include the following Fields
Audit_ID,
Audit_API (or record ID)
Audit_FormName,
Audit_FieldName
Audit_OldValue
Audit_Value
Audit_ChangedBy
AuditChangeType
Audit_Updates
Audit_DateTime

I altered the code so that every place the original code would write the control on the form it would also write to my new table.

My changes are highlighted in yellow below:

Public Function AuditDataTrail()
'Instructions for the Audit Trail Function
'
'1. Open your Access program and add a new module.  Copy and paste the
'   function (in gray) to this module, then save and close the module.
'2. Open the table you want to audit in table design view and add a new
'   Memo field called 'Updates'.
'3. Open the form that references the table and add this field to your secured
'   form.  Set the Locked property to Yes.
'4. In the form properties window, add the following to
'   the BeforeUpdate property: =AuditData()



' Comments   : This function is used to provide an audit trail of all changes to a record
'        : entered on a secured form.  After adding a locked memo field called 'Updates'
'        : on the source form, add a reference to the Before Update event to this function.
' Requirement: A bound memo field named 'Updates' must exist on the active form
' Parameters : None
' Returns    : Appends Audit Trail data as a string to the memo field on the active form
' Created By : Ofni Systems Inc.
' Modified   : 04/07/01
' Modified   : SWT  July 2010
'
' Additional Instructions to write each change to a table
' which will provide a method to query the table by FormName, Field or control Name, dates,
' userID, changetype, oldvalues etc.
'
[highlight]'5. Create a table named audit_Changes and include the following Fields
'   Audit_ID, Audit_API (or record ID), Audit_FormName, Audit_FieldName
'   Audit_OldValue, Audit_Value,Audit_ChangedBy, AuditChangeType, Audit_Updates, Audit_DateTime[/highlight]

' --------------------------------------------------------
Dim frmActive As Form
Dim ctlData As Control
Dim strEntry As String
[highlight]Dim db As DAO.Database
Dim strAPI As String        'API or WellID or ID for record
Dim strChangeType As String  'ie Record Added, Changed or Deleted
Dim rstChanges As DAO.Recordset
Dim strAuditEntry As String  'uses the variable to write to the table vs. the memofield on the form.[/highlight]


On Err GoTo NextCtl
    [highlight]Set db = CurrentDb()[/highlight]
    Set frmActive = Screen.ActiveForm
    
    'When using subforms use the following statements where "Main Form" is the name of the Parent Form
    ' Added by SWT
    
    If frmActive.Name = "Main Form" Then
    Set frmActive = Screen.ActiveControl.Parent
    
    End If
    
    'Determine who, when and where
    'Original code
    'strEntry = " by " & Application.CurrentUser & " on " & Now
    
    'Determine who, when and where using Windows Logon
     strEntry = " by " & fOSUserName() & " on " & Now
    
    ' If new record, record it in audit trail and exit sub.
    If frmActive.NewRecord = True Then
       frmActive!Updates = frmActive!Updates & "New Record Added" & strEntry
       'this line added by SWT to get the UserCreated filled.
       frmActive!UserCreated = fOSUserName()
       frmActive!DateCreated = Now()
       
              
                    
       'Uncomment the next line if you do not want to record the details of the initial entry
       'Exit Function
    End If
    
    'Check each data entry control for change and record
    For Each ctlData In frmActive.Controls
     
[highlight]'this line should grab the API from the MainForm
     ' but for test purposes I gave it the ID of "test"
        strAPI = "test" 'Forms![Main Form]!cbo_SelectWell[/highlight] 
       ' Only check data entry type controls.
       Select Case ctlData.ControlType
          Case acTextBox, acComboBox, acCheckBox, acOptionButton
             ' Skip Updates field.
            If ctlData.Name = "Updates" Then GoTo NextCtl
            
            'Added by SWT
            'keeps the DateModified and UserModified field changes from appending to "Updates"
            If ctlData.Name = "DateModified" Then GoTo NextCtl
            If ctlData.Name = "UserModified" Then GoTo NextCtl
            
            
            'Skip unbound controls (3 is ControlSource)
            If ctlData.Properties(3) = "" Then GoTo NextCtl
            
             
             Select Case IsNull(ctlData.Value)
                'Check for deleted data
                Case True
                    If Not IsNull(ctlData.OldValue) Then
                        frmActive!Updates = frmActive!Updates & Chr(13) & Chr(10) & "  " & ctlData.Name & " Data Deleted: Old value was '" & ctlData.OldValue & "'" & strEntry
       [highlight]             strAuditEntry = frmActive!Updates & Chr(13) & Chr(10) & "  " & ctlData.Name & " Data Deleted: Old value was '" & ctlData.OldValue & "'" & strEntry
                    strChangeType = "Deleted"
                    
                    'Write to Table "audit_Changes"
                    Set rstChanges = db.OpenRecordset("audit_Changes")
                           With rstChanges
                                .AddNew
                                        ![Audit_API] = strAPI
                                        ![Audit_FormName] = frmActive.Name
                                        ![Audit_FieldName] = ctlData.Name
                                        ![Audit_OldValue] = ctlData.OldValue
                                        '![Audit_Value] = ctlData.Value
                                        ![Audit_ChangedBy] = fOSUserName()
                                        ![Audit_ChangeType] = strChangeType
                                        ![Audit_Updates] = strAuditEntry
                                        ![Audit_DateTime] = Now()
                                      .Update
                                      rstChanges.Close
                                End With [/highlight]
                    
                    End If
                   
                'Check for new or changed data
                Case False
                    If IsNull(ctlData.OldValue) And Not IsNull(ctlData.Value) Then
                        frmActive!Updates = frmActive!Updates & Chr(13) & Chr(10) & "  " & ctlData.Name & " Data Added: " & ctlData.Value & strEntry
            [highlight]          strAuditEntry = frmActive!Updates & Chr(13) & Chr(10) & "  " & ctlData.Name & " Data Deleted: Old value was '" & ctlData.OldValue & "'" & strEntry
                        strChangeType = "Added"
'
                        'Write to Table "audit_Changes"
                        Set rstChanges = db.OpenRecordset("audit_Changes")
                        With rstChanges
                                .AddNew
                                    ![Audit_API] = strAPI
                                    ![Audit_FormName] = frmActive.Name
                                    ![Audit_FieldName] = ctlData.Name
                                    ![Audit_OldValue] = ctlData.OldValue
                                    ![Audit_Value] = ctlData.Value
                                    ![Audit_ChangedBy] = fOSUserName()
                                    ![Audit_ChangeType] = strChangeType
                                    ![Audit_Updates] = strAuditEntry
                                    ![Audit_DateTime] = Now()
                                  .Update
                                  rstChanges.Close
                        End With [/highlight]
                        
                    'If control had previous value, record previous value.
                    ElseIf ctlData.Value <> ctlData.OldValue Then
                        frmActive!Updates = frmActive!Updates & Chr(13) & Chr(10) & "  " & ctlData.Name & " changed from '" & ctlData.OldValue & "' to '" & ctlData.Value & "'" & strEntry
           [highlight]         strAuditEntry = frmActive!Updates & Chr(13) & Chr(10) & "  " & ctlData.Name & " changed from '" & ctlData.OldValue & "' to '" & ctlData.Value & "'" & strEntry
                    strChangeType = "Changed"
'
                        'Write to Table "audit_Changes"
                            Set rstChanges = db.OpenRecordset("audit_Changes")
                           With rstChanges
                                .AddNew
                                        ![Audit_API] = strAPI
                                        ![Audit_FormName] = frmActive.Name
                                        ![Audit_FieldName] = ctlData.Name
                                        ![Audit_OldValue] = ctlData.OldValue
                                        ![Audit_Value] = ctlData.Value
                                        ![Audit_ChangedBy] = fOSUserName()
                                        ![Audit_ChangeType] = strChangeType
                                        ![Audit_Updates] = strAuditEntry
                                        ![Audit_DateTime] = Now()
                                      .Update
                                      rstChanges.Close
                                End With [/highlight]

                    
                    End If
                    
                   If frmActive.NewRecord = True Then
                   frmActive!UserModified = ""
                   frmActive!DateModified = ""
                   Else
                    frmActive!UserModified = fOSUserName()
                    frmActive!DateModified = Now()
                    End If
            End Select
   
   End Select

NextCtl:
   Next ctlData
  
End Function

I hope that posting this solution will help someone else.