AuditTrail() - where is data stored? 2

[VegasUser]

I'm probably overlooking the obvious -
I'm using the MS AuditTrail() module and have changed it to explicitly reference a bound control on my form; [txtAudit]. I'm calling the module from the BeforeUpdate event on the form and it's working fine, but the data isn't being stored in the field.

I need to be able to query on this data.

Any help would be appreciated!
Thx!

 

[MichaelRed]

I'm not familiar w/ the specific module you refer to. I have written one of my own (see faq181-291) here. If hte module code is available (and if it is fr Ms. A. it should be), then hte storage would have to be declared at the top of the function. It MIGHT be a user supplied argument, but then the "docs" (perhaps commentary in the code or with the module / function) need to both identify and name the object. It almost has to be one or more tables, depending on the specific design. If you look at the faq I ref, you should be able to readily see how I did it. Ms. may be better at this (doc stuff) than I am, so given you understand mine, theirs should be easier (to understand).



MichaelRed

 

[willir]

Are you referring to...
How to create an audit trail of record changes in a form in Access 2000 ??

Open the Customers table in Design view and add a new field called Updates. Set the data type of the field to Memo. Close and save the table.

           MyForm!Updates = MyForm!Updates & Chr(13) & _
           Chr(10) & C.Name & "--previous value was blank"

Per the instructions, the module updates a control (text box) on the form that the instructions asked you to create.

Richard

 

[VegasUser]

A star for both!
I implemented willir's suggestion, but I am still not getting the memo data to store in the Updates field - it shows up in the Updates control though.
I'm going to try MichaelRed's module since I encountered trouble with audits on some relational tables. I think the single history table may work best.
Thanks again!
What happens in Vegas stays in Vegas! :0)

 

[willir]

but I am still not getting the memo data to store in the Updates field - it shows up in the Updates control though

The way Microsoft wrote this module, the value is not stored in a table.

You can add a tweak to accomplish what you want...

Define a table as follows (or tweak the code to match your needs)...
tblAudit
TimeDateStamp - date
UserName - text
FormName - text
AuditNote - memo

Primary key could be TimeDateStamp, or TimeDateStamp + UserName, or TimeDateStamp + FormName depending on usage and needs.

Add the code in blue.

Function AuditTrail()
On Error GoTo Err_Handler

'This code was copied from Microsoft's web site...
'[URL unfurl="true"]http://support.microsoft.com/?kbid=197592[/URL]
[COLOR=blue]
    Dim rst as DAO.Recordset[/color]
    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 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
[COLOR=blue]
    If Len(MyForm!Updates & "") Then

       Set rst = CurrentDB.OpenRecordset("tblAudit")
       With rst
          .AddNew
          !TimeDateStamp = Now()
          !FormName = MyForm!Name
          !UserName = environ("user")
          !AuditNote = MyForm!Updates
          .Update
       End With
       Set rst = Nothing

    End If
[/color]
TryNextC:
    Exit Function

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

Richard

 

[VegasUser]

I've changed your code only to add site_key instead of FormName as my primary key for tblAudit. The new memo field is still not being updated, the others are.

The Updates field is on a Tab named "Pg_Update" that is captioned "Audit Trail". I've checked all of my naming to make sure I don't have a conflict. Is there another way to reference that control on a tab? I've tried Myform!Pg_Update!Updates but get Error 438.

Other than that - this works great!
thanks again -

 

[VegasUser]

Never mind - I feel REALLY stupid!
The first line of the memo field was a NL/CR so I didn't see the data until I expanded the field.
Sorry! You've been a great help - this works exactly like I wanted it to. It even resolves my audits on related tables.
Thanks again -