keeping an audit trail

J

JohnE

Hello all. I have a form in which is only used to add or update records. A
field on the table and field in the form is for keeping track of all updates
of each record at the record level. I am using the following coding to do
so. The table field is called ProjectDevelopmentApplicationAudit and the
field on the form is called txtProjectDevelopmentApplicationAudit.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Err GoTo TryNextC
Dim MyForm As Form
Dim ctl As Control
Dim strUser As String
Set MyForm = Screen.ActiveForm
strUser = fOSUserName


' Set date and current user if form has been updated.
MyForm!txtProjectApplicationDevelopmentAudit =
MyForm!txtProjectApplicationDevelopmentAudit & Chr(13) & Chr(10) & vbCrLf & _
"Record changed or modified on " & Now & " by " & strUser & ";"

' If new record, record it in audit trail and exit sub.
If MyForm.NewRecord = True Then
MyForm!txtProjectApplicationDevelopmentAudit =
MyForm!txtProjectApplicationDevelopmentAudit & Chr(13) & Chr(10) & "NEW
RECORD"
Exit Sub
End If

' Check each data entry control for change and record old value of Control.

For Each ctl In MyForm.Controls

' Only check data entry type controls.
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
If ctl.Name = "txtProjectApplicationDevelopmentAudit" Then GoTo
TryNextC ' Skip txtProjectApplicationDevelopmentAudit field.

If ctl.Value <> ctl.OldValue Then
MyForm!txtProjectApplicationDevelopmentAudit =
MyForm!txtProjectApplicationDevelopmentAudit & Chr(13) & Chr(10) & "CHANGED
or MODIFIED" & vbCrLf & _
" " & "FIELD: " & ctl.Name & vbCrLf & " " & "FROM:
" & ctl.OldValue & _
vbCrLf & " " & "TO: " & ctl.Value
End If
End Select
TryNextC:
Next ctl

End Sub

I do not get any error messages but I don't see any info in the table field
either. I am in a quandry over this. If anyone can assist and see what is
wrong with this, I say thank you in advance.
*** John
 
J

JohnE

Hi there. Please disregard the issue below. I finally figured it out.
Thank you for taking the time to look at this.
*** John
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top