Help with Function in Access

  • Thread starter Thread starter Sandspirit
  • Start date Start date
S

Sandspirit

I have downloaded a Funtion for Audit trail changes made to forms. It
will place the information in a memo field. I have pasted the
function in a new module.. but at the form_BeforeUpdate() how do I
notate to use the function???? I know, it seems silly, but I keep
gettting errors. The name of the function is AuditData.

Thanks!


Sandspirit
 
I managed to figure out how to set a macro at the BeforeUpdate for the
form to run the code, but it still did not work. ;;(((

-sandspirit
 
Sandspirit said:
I managed to figure out how to set a macro at the BeforeUpdate for the
form to run the code, but it still did not work. ;;(((

-sandspirit
Click to expand...
You really need to give us more information, as we have no idea what
your AuditTrail function is or what it's supposed to do.

You also aren't saying what's not working.
 
I apologize. This is the function I downloaded:

Public Function MyAuditData()

' Comments : This function is used to provide an audit trail of all
changes to a record

Dim frmActive As Form
Dim ctldata As Control
Dim mySQL As String
Dim mySQL2 As String
Dim mySQL3 As String

On Err GoTo NextCtl

Set frmActive = Screen.ActiveForm

'For new records
mySQL = "INSERT INTO tblAuditTrail (UserName, Timestamp, Value, Field,
Action) VALUES ( """ & CurrentUser & """, Now(), ctldata.value,
ctldata.name, 'New Record Added')"

'For edits
mySQL2 = "INSERT INTO tblAuditTrail (UserName, Timestamp, Value,
OldValue, Field, Action) VALUES ( """ & CurrentUser & """, Now(),
ctldata.value, ctldata.oldvalue, ctldata.name, 'Edited Record')"

'For deleted data
mySQL3 = "INSERT INTO tblAuditTrail (UserName, Timestamp, Value,
OldValue Field, Action) VALUES ( """ & CurrentUser & """, Now(),
ctldata.value, ctldata.oldvalue, ctldata.name, 'Data Deleted')"



' If new record, record it in audit trail and exit sub.
If frmActive.NewRecord = True Then

DoCmd.RunSQL mySQL

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

DoCmd.RunSQL mySQL3

End If

'Check for new or changed data
Case False
If IsNull(ctldata.OldValue) And Not IsNull(ctldata.Value) Then


DoCmd.RunSQL mySQL2

'If control had previous value, record previous value.
ElseIf ctldata.Value <> ctldata.OldValue Then

DoCmd.RunSQL mySQL2



End If

End Select

End Select

NextCtl:
Next ctldata
End Function


Thanks!

-Sandspirit
 
Sandspirit said:
I apologize. This is the function I downloaded:
Click to expand...

And what's not working? Are you getting an error somewhere?

If you want to call the function, you simply use the function name, such as:

MyAuditData

The function is written pretty generic, so that it references the
current form and all the controls on it. That lets it be used on any
form for the auditing.

Make sure you have a table created called tblAuditTrail that has the
necessary fields, such as UserName, TimeStamp, etc.

I also would change the code and the table to not use any reserved words
such as: Value and Field.

This function also assumes that certain values exist 'somewhere'. The
original designer must have had functions somewhere to track items such
as "CurrentUser". If you don't have a value for that, it's going to fail.
 
Back
Top