Cant see entry in Table

[aids2]

I have just set up a basic Audit Trail using the code shown below. However when I go to the underlying table the data isnt visible, I need a function that will allow the users to print the data from the underlying table if required.
Any help would be appreciated.

Here is the code used:

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


' --------------------------------------------------------
Dim frmActive As Form
Dim ctlData As Control
Dim strEntry As String

On err GoTo NextCtl

Set frmActive = Screen.ActiveForm

'Determine who, when and where
strEntry = " by " & Application.CurrentUser & " 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
'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

'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
End Select
End Select
NextCtl:
Next ctlData
End Function

 

[KenReay]

Hi

You say the data is not visible, do you mean that it is not being written to the table?

I can see nothing in your posted code which writes a record to the table, the form frmActive is clearly a bound form (frmActive.NewRecord), but is it bound to the table holding the audit trail, or the table being updated?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[aids2]

Hi
That is correct it is not being written to the table. I need the data visible in the table so that I can use it in reports and queries.
If you can give me some pointers as to what I need to do it would be appreciated.

 

[KenReay]

Hi

You did not answer my question, but making an assumption that the form frmActive is not bound to the Audit TRail table, and that Updates is an unbound text box, then code similar to :

DoCmd.runSQl "INSERT INTO tblAuditTrail (txtUpdates) VALUES (" & chr(34) & Updates & chr(34) & ");"

in the after update event of the form would do it

Note I have had to make assumptions about your table and column names, since you have not given this information

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[aids2]

Sorry for taking so long to get back.
I must apologise for not giving you all the answers you asked for.
The field is bound to a table called document register and the field is called Updates.

So I require the Audit trail data to be inserted into the table document register and the field Updates.
I hope this provides you with all the necessary information.