audit history 1

[RandyMyers]

Hello All,

I use SQL databases and Access front ends mainly connecting through ODBC.

Is there an easy way of tracking (auditing) changes made in the system? What I want is basically User A changes table B on date, old value is, new value is.

I have a method that can do this using a function that is tied to the Before Update event on a form. It works fine for standard forms, but does not work for sub forms. I have figured a method of always opening a popup form to make a change in the displayed sub form. This works but I believe there must be an easier way.

Anybody ever do this and find a good way of writing changed out to a history table?

 

[PHV]

Why not using Triggers in the SQL databases ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RandyMyers]

I have never used triggers. Would you please give me a quick example of how this would work. I am sure I could expand from there.

 

[jsteph]

Randy,
You should google 'sql server triggers', or look in books-online from sql-server documentation.

However, you really, really need to be careful with triggers. If there is any problem with the trigger--it could lock up that record or that table indefinitely, or cause other application errors that you may have no idea have anything to do with the trigger. Further, they are very difficult to debug, since the error messages can be cryptic and very misleading, and won't say anything about the trigger possibly being at the root of some obscure error you get.

Do as little as possible in the trigger, and make it as clean and perfectly coded as possible, and you should be ok.
--Jim

 

[cmmrfrds]

I agree on the use of triggers for this purpose. Also, please read the help documentation. Here are a couple examples. The thing to note is that on an update sql server creates 2 hidden tables called inserted and deleted. The deleted table will have the before image and inserted will have the new image. The example is for updating the modified date on an invoice header and invoice detail record(s).

Example of Single record trigger.
create trigger trig_ModDate_InvoiceHeader
on dbo.InvoiceHeader
for update
as
declare @ID int
select @ID = invoiceHeaderID from deleted
UPDATE InvoiceHeader
SET modifyDate = getdate()
WHERE invoiceHeaderID = @ID

Example of Mulitple record trigger.
Parent/Child relationship.
create trigger trig_ModDate_InvoiceDetail
on InvoiceDetail
for update
as
declare @ID int
select @ID = invoiceID from deleted
if @@rowcount = 1
BEGIN
UPDATE InvoiceDetail
SET modifyDate = getdate()
WHERE invoiceID = @ID
END
ELSE
BEGIN
UPDATE InvoiceDetail
SET modifyDate = getdate()
WHERE InvoiceDetail.invoiceID IN
(Select InvoiceDetail.invoiceID from inserted)
END

 

[Turb]

Randy,

I have a method that can do this using a function that is tied to the Before Update event on a form.

Would you share this method? This is exactly what I have need of.

Thank you,
Turb

Ind. Engineering Tech.

http://www.cdstandbypower.com/

 

[RandyMyers]

On the Before Update event on either the form or an individual field:
-----------------------------------------------------------

Call basLogTrans(Me, "Key Filed Name", KeyField)


The function:
-----------------------------------------------------------
Public Function basLogTrans(Frm As Form, MyKeyName As Variant, MyKey As Variant) As String

Dim MyDb As DAO.Database
Dim MyCtrl As Control
Dim MyMsg As String
Dim Hist As String

For Each MyCtrl In Frm.Controls
If (basActiveCtrl(MyCtrl)) Then
If ((MyCtrl.Value <> MyCtrl.OldValue) _
Or (IsNull(MyCtrl) And Not IsNull(MyCtrl.OldValue)) _
Or (Not IsNull(MyCtrl) And IsNull(MyCtrl.OldValue))) Then
Hist = "dbo_HistoryTable"
Call basAddHist(Hist, Frm.Name, MyKey.Name, MyCtrl)
End If
End If
Next MyCtrl

basLogTrans = True

End Function
--------------------------------------------------------
Public Function basActiveCtrl(Ctl As Control) As Boolean

Select Case Ctl.ControlType
Case Is = acTextBox
basActiveCtrl = True
Case Is = acLabel
Case Is = acRectangle
Case Is = acLine
Case Is = acImage
Case Is = acCommandButton
Case Is = acOptionButton
Case Is = acCheckBox
basActiveCtrl = True
Case Is = acOptionGroup
Case Is = acBoundObjectFrame
Case Is = acListBox
basActiveCtrl = True
Case Is = acComboBox
basActiveCtrl = True
Case Is = acSubform
Case Is = acObjectFrame
Case Is = acPageBreak
Case Is = acPage
Case Is = acCustomControl
Case Is = acToggleButton
Case Is = acTabCtl
End Select

End Function
-----------------------------------------------------------
Public Function basAddHist(Hist As String, Frm As String, MyKeyName As String, MyCtrl As Control)

Dim dbs As DAO.Database
Dim tblHistTable As DAO.Recordset

Set dbs = CurrentDb
Set tblHistTable = dbs.OpenRecordset(Hist, dbOpenDynaset, dbSeeChanges)

With tblHistTable
.AddNew
!MyKey = Forms(Frm).Controls(MyKeyName)
!MyKeyName = MyKeyName
!frmName = Frm
!FldName = MyCtrl.ControlSource
!dtChg = Now()
!UserId = Environ("Username")
!OldVal = MyCtrl.OldValue
!NewVal = MyCtrl
.Update
End With

End Function

----------------------------------------------------------
This requires a HistoryTable with the fields:
RecID (Identity field)
MyKey
MyKeyName
frmName
FldName
dtChg
UserId
OldVal
NewVal

It also only works on forms; I can not get it to work on subforms. I have had to build new record and edit record popups for records in a subforms that I want to audit track.

If anyone has an idea of how to get this to track changes on subforms I would love to know since it would certainly make the form design easier.

 

[Turb]

Randy,
Thank you!

Turb

Ind. Engineering Tech.

http://www.cdstandbypower.com/

 

[jsteph]

Randy,
Have you tried passing the subform to your function basLogTrans?

Use this syntax:
Call basLogTrans(Me!MysubForm.Form, "Key Filed Name", KeyField)
--Jim

 

[jsteph]

...sorry, you should call that from the subform's AfterUpdate, not the parent form...so from the subform, use:
Me.Parent(Me.Name).Form
--Jim

 

[RandyMyers]

Ok, I tried putting the code on the subforms AfterUpdate event. I do not get any errors, however the changes are not recorded in the History table either.

Here is the code I am using...

Call basLogTrans(Forms!frmCashJournalPopup!frmCashJournalSub.Form, "Receipts", Receipts)

Where frmCashJournalPopup is the parent form and frmCashJournalSub is the sub form.

 

[jsteph]

Randy,
What happens when debugging? Have you put a breakpoint in the subform's afterUpdate event and followed-the-yellow-line-road?
--Jim

 

[RandyMyers]

Hello Jim,

Tracing through, each of the ctrls are checked by calling the basActiveCtrl function, however it never calls the basAddHist function which would write the data to the table. It appears as if the check for data change:

If ((MyCtrl.Value <> MyCtrl.OldValue) _
Or (IsNull(MyCtrl) And Not IsNull(MyCtrl.OldValue)) _
Or (Not IsNull(MyCtrl) And IsNull(MyCtrl.OldValue))) Then

is never returning a positive result so the:

Call basAddHist(Hist, Frm.Name, MyKey.Name, MyCtrl)

statement is never ran, therefore the function to write to the table is never ran. Instead it pops form the check straight to the End If statement.

Would this be because the statement is on the Adter Update event, insatead of the Before Update event?

If I put the statement on the Before Update event it give an error 2450

...can't finf the form 'frmCahsJournalSub' referred to ...


So it can't find the Sub Form.

This works perfect on a primary or parent form...

 

[tman72]

I've experienced this type of an error before and resolved it by selecting the form in code prior to execution of a procedure. Something like:

DoCmd.SelectObject acForm, "MYFORM"

Where MYFORM should be replaced with the name of the form. Not sure if it will help your situation, but thought I'd put it out there for you to try at least.

 

[RandyMyers]

If I put this type of code on the After Update event:

DoCmd.SelectObject acForm, "Forms!frmCashJournalPopup!frmCashJournalSub.Form"
Call basLogTrans(Forms!frmCashJournalPopup!frmCashJournalSub.Form, "Receipts", Receipts)

I get an error 2489, the object "Forms!frmCashJournalPopup!frmCashJournalSub.Form" isn't open...

If I put it on the Before Update event the system locks.

 

[jsteph]

Would this be because the statement is on the Adter Update event, insatead of the Before Update event?

Yes, my mistake. However, audit triggers on Before_update aren't as reliable because the update can be cancelled.

To make it work in the beforeUpdate you need the *controlname* of the subform, which may not be the formname, so that was my mistake in using the "me.name" for the subform--that's the object's name, again you need the control name.

For the Afterupdate, you can save an 'old value' variable for each control, setting these on the beforeupdate event when you have the oldvalue available.
--Jim

 

[RandyMyers]

Ok, I can't seem to make this work. Maybe do not understand what you mean by control name. The forms name is frmCashJournalSub.... How would I find out if this is in fact it's control name?

 

[assets]

Also have a look at the microsoft site. I used a mod of this to track changes to forms. It has a memo field in each table that show what the field was before the change and the date and user who changed it.


Never give up never give in.

There are no short cuts to anything worth doing

 

[jsteph]

Randy,
In design view of the main form, select the empty white box that is the subform, and right-click and select Properties. There will be a Name and a Source Object. The Name is the name of the control, as it exists on the main form--keep in mind that this same subform can exist on any number of main forms with a different Name for each, even though it's own 'name' is always the same.

Often, the Wizard will name the the Source Object and the Name the same, but they don't need to be the same. Whenever referring to anything on this subform, you must use the Name property, and then qualify it with the .Form property of the control. Now, it's just like any other form, and this whole path can be stored as a Form object variable as any other form.
--Jim

 

[RandyMyers]

Jim,

I really appreciate your help. What you are saying is what I thought was the case.

I put this statement on the subform's Before Update event:

Call basLogTrans(Forms!frmCashJournalPopup!frmCashJournalSub.Form, "Receipts", Receipts)

Where the main form is frmCashJournalPopup and hte subform is frmCahsJournalSub. An error when the record is being updates occurs, can't find the form frmCashJournalSub and by debugging I see that it is the statement in the function:

!MyKey = Forms(Frm).Controls(MyKeyName)

where it is erroring out. I believe it does not recognize the subform as a form for this. Any other thoughts?