Re: Form Calculation writes record to Audit Log
- From: Tom van Stiphout <no.spam.tom7744@xxxxxxx>
- Date: Fri, 21 Sep 2007 07:41:39 -0700
On Fri, 21 Sep 2007 13:57:04 GMT, "ARC" <acolonna35@xxxxxxxxxxx>
wrote:
Just some observations:
LogType: Rather than passing in a string and converting it to a
number, create an Enum:
Enum enumLogTypes 'Keep in sync with tblLogTypes
LT_Quote = 1
LT_Invoice
LT_Payment
'etc.
End Enum
Then change this function to:
Public Function AuditLog(LogType As enumLogTypes, ...etc.
Benefits: faster because the entire Case statement can be omitted, and
you get nice intellisense while calling the function.
tblLogTypes (I wasn't sure if you have one) will later help with audit
reports.
You may want to cache that UseAuditLog flag in a global variable
rather than hit the db every time you call the AuditLog function.
Btw, your test "If Not rs.BOF " comes too late. You already did
"rs.MoveFirst" which only works if there is a record.
Formatting rs!LogDate is not useful, assuming this is a date field.
Just write:
rs!LogDate=Now()
or set this as the default in the table.
I think:
db.Close
is not useful on CurrentDB. See usenet for lengthy discussions about
..Close and set to Nothing.
-Tom.
What I did was to create an Audit log function where I only run the function.
for certain things. For example, on an invoice, if the user changes the
invoice status, in the after_update code, I'll call:
Call AuditLog("InvoiceStatus", "Changed Status from Delivered to Ordered,
Invoice #: " & Forms!frmOpt.Form!InvoiceNo & ", Invoice Amount: " &
Format(Me!txtTotalCalc, "Currency") & ", Customer: " &
Forms!frmOpt.Form!cboCustomer.Column(5))
So basically I'm passing everying I want to write to the Audit Log proc,
which will then add in the machine name and time/date stamp. Here's the
Audit Log proc:
Public Function AuditLog(LogType As String, LogDesc As String) As Boolean
On Error GoTo ErrRtn
Dim db As Database, rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("tGlobal", DB_OPEN_SNAPSHOT)
rs.MoveFirst
If Not rs.BOF Then
If rs!UseAuditLog = 0 Then
rs.Close
db.Close
Exit Function
End If
End If
rs.Close
Set rs = db.OpenRecordset("tAudit", DB_OPEN_DYNASET)
rs.AddNew
rs!LogDate = Format(Now(), "General Date")
rs!LogUser = CurrentMachineName()
Select Case LogType
'1;"Deleted Quotes";2;"Deleted Invoices";3;"Deleted Payments";4;"Deleted
Jobs";5;"Deleted Parts";6;"Deleted Customers";7;"Deleted
Purchase";8;"Changed Inv. Status"
Case "Quote"
rs!LogAction = 1
Case "Invoice"
rs!LogAction = 2
Case "Payment"
rs!LogAction = 3
Case "Job"
rs!LogAction = 4
Case "Part"
rs!LogAction = 5
Case "Customer"
rs!LogAction = 6
Case "Purchase"
rs!LogAction = 7
Case "InvoiceStatus"
rs!LogAction = 8
End Select
rs!LogDesc = LogDesc
rs.Update
rs.Close
db.Close
Exit Function
ErrRtn:
MsgBox Err.Number & " - " & Err.Description & "Function: AuditLog",
vbCritical
Exit Function
End Function
- Follow-Ups:
- References:
- Form Calculation writes record to Audit Log
- From: FrozenDude
- Re: Form Calculation writes record to Audit Log
- From: ARC
- Form Calculation writes record to Audit Log
- Prev by Date: Re: duplicates query help & strategy for update queries with SetWarnings = False
- Next by Date: Altova Software v2008 (with Access support) is available
- Previous by thread: Re: Form Calculation writes record to Audit Log
- Next by thread: Re: Form Calculation writes record to Audit Log
- Index(es):
Relevant Pages
|
