Question A snag in audit trail...records overwritten?

[connie]

About a month ago I put together a database with an audit trail. It is a database of equipment that gets edited from time to time through Equipment Change Requests (ECR's) via a form I designed. I thought everything was fine & dandy until I went to run a custom query/report on ECR # 12 to see its effect on the overall budget. Then I realized that my audit trail - which has been tracking for a month now, about 1,000 changes already - may have a defect.
The database itself stores the current ECR_No, Reason_for_Change, and Ext_Cost.
The form that changes are made through (attached) prompts through VBA a separate table called tblDatabaseChanges to record the following:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblDatabaseChanges")
    
With rs
      .AddNew
      .Fields("RecordID") = Me.RecordID
      .Fields("Project_Name_Old") = Me.ctlProjectName.OldValue
      .Fields("Major_Dept_Old") = Me.ctlMajorDept.OldValue
      .Fields("Dept_Name_Old") = Me.ctlDeptName.OldValue
      .Fields("Room_Name_Old") = Me.ctlRoomName.OldValue
      .Fields("Room_No_Old") = Me.ctlRoomNo.OldValue
      .Fields("Item_Qty_Old") = Me.ctlItemQty.OldValue
      .Fields("Unit_Cost_Old") = Me.ctlUnitCost.OldValue
      .Fields("Ext_Cost_Old") = Me.ctlExtCost.OldValue
      .Fields("Manufacturer_Old") = Me.ctlManufacturer.OldValue
      .Fields("Model_Old") = Me.ctlModel.OldValue
      .Fields("Phase_Old") = Me.ctlPhase.OldValue
      .Fields("ECR_No_Old") = Me.ctlECRNo.OldValue
      .Fields("CAD_ID_Old") = Me.ctlCADID.OldValue
      .Fields("Description_Old") = Me.ctlDescription.OldValue
      .Fields("Type_Funding_Old") = Me.ctlTypeFunding.OldValue
      .Fields("FI_Old") = Me.ctlFI.OldValue
      .Fields("AC_Old") = Me.ctlAC.OldValue
      .Fields("Last_Edited_by_Old") = Me.ctlLastEditedBy
      .Fields("Reason_for_Change_Old") = Me.ctlReasonForChange
      .Fields("Budget_Variance") = Me.ctlExtCost.OldValue - Me.ctlExtCost
      .Update
      .Close
End With
 
  Set db = Nothing
  Set rs = Nothing

End Sub

There have since been further ECR's completed, so I'll have a record where the ECR No. is for example 15 and so ECR 12 gets pushed into the old value field and wouldn't be found by the query/report. And from the looks of it, once a 3rd ECR is entered for the same equipment item, the old values and changes are eventually overwritten.

How can I salvage this audit trail? Please help....this was the result of SO much work and I'm now brick wall*forehead

What I would love is for the form - which cycles all the records in the database - to not cycle the fields ECR_No, Last_Edited_by, and Reason_for_Change, and instead write the records into tblDatabaseChanges and when you go back to that record via the form those fields only remain fresh & blank for each item. Without, if possible, creating a whole new dilemma with the existing audit trail info.

 

[connie]

Audit trail snag

About a month ago I put together a database with an audit trail. It is a database of equipment that gets edited from time to time through Equipment Change Requests (ECR's) via a form I designed. I thought everything was fine & dandy until I went to run a custom query/report on ECR # 12 to see its effect on the overall budget. Then I realized that my audit trail - which has been tracking for a month now, about 1,000 changes already - may have a defect.
The database itself stores the current ECR_No, Reason_for_Change, and Ext_Cost.
The form that changes are made through (attached) prompts through VBA a separate table called tblDatabaseChanges to record the following:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblDatabaseChanges")
    
With rs
      .AddNew
      .Fields("RecordID") = Me.RecordID
      .Fields("Project_Name_Old") = Me.ctlProjectName.OldValue
      .Fields("Major_Dept_Old") = Me.ctlMajorDept.OldValue
      .Fields("Dept_Name_Old") = Me.ctlDeptName.OldValue
      .Fields("Room_Name_Old") = Me.ctlRoomName.OldValue
      .Fields("Room_No_Old") = Me.ctlRoomNo.OldValue
      .Fields("Item_Qty_Old") = Me.ctlItemQty.OldValue
      .Fields("Unit_Cost_Old") = Me.ctlUnitCost.OldValue
      .Fields("Ext_Cost_Old") = Me.ctlExtCost.OldValue
      .Fields("Manufacturer_Old") = Me.ctlManufacturer.OldValue
      .Fields("Model_Old") = Me.ctlModel.OldValue
      .Fields("Phase_Old") = Me.ctlPhase.OldValue
      .Fields("ECR_No_Old") = Me.ctlECRNo.OldValue
      .Fields("CAD_ID_Old") = Me.ctlCADID.OldValue
      .Fields("Description_Old") = Me.ctlDescription.OldValue
      .Fields("Type_Funding_Old") = Me.ctlTypeFunding.OldValue
      .Fields("FI_Old") = Me.ctlFI.OldValue
      .Fields("AC_Old") = Me.ctlAC.OldValue
      .Fields("Last_Edited_by_Old") = Me.ctlLastEditedBy
      .Fields("Reason_for_Change_Old") = Me.ctlReasonForChange
      .Fields("Budget_Variance") = Me.ctlExtCost.OldValue - Me.ctlExtCost
      .Update
      .Close
End With
 
  Set db = Nothing
  Set rs = Nothing

End Sub

There have since been further ECR's completed, so I'll have a record where the ECR No. is for example 15 and so ECR 12 gets pushed into the old value field and wouldn't be found by the query/report. And from the looks of it, once a 3rd ECR is entered for the same equipment item, the old values and changes are eventually overwritten.

How can I salvage this audit trail? Please help....this was the result of SO much work and I'm now brick wall*forehead

What I would love is for the form - which cycles all the records in the database - to not cycle the fields ECR_No, Last_Edited_by, and Reason_for_Change, and instead write the records into tblDatabaseChanges and when you go back to that record via the form those fields only remain fresh & blank for each item. Without, if possible, creating a whole new dilemma with the existing audit trail info.

 

[irish634]

Re: Audit trail snag

I have mine setup a tad different but the first thing that comes to mind from your description is: Do you have your main equipment table and tblDatabaseChanges relationship set correctly? By the sounds of it the relationship is set to one to one instead of one to many.

 

[connie]

Re: Audit trail snag

I have mine setup a tad different but the first thing that comes to mind from your description is: Do you have your main equipment table and tblDatabaseChanges relationship set correctly? By the sounds of it the relationship is set to one to one instead of one to many.

Thanks so much for responding...my ulcer eased a tad just at the Reply to Thread notification!

So I really thought the relationship I'd set up was a one to many, but when I went in to
verify that now, it's telling me the Relationship Type is Indeterminate! When I click on join type, the 3rd option is selected: "Include ALL records from 'tblDatabaseChanges' and only those records from 'tblEquipmentDatabase' where the joined fields are equal."

 

[jzwp22]

Since you are adding a new record each time, I cannot see why you would lose or overwrite records. Can someone be changing existing records in the audit table without adding a new one? Can someone delete an existing record? Your users should never see or have access to the audit table.

I don't see a field for the equipment that the ECR pertains to (recordID?), nor do I see a date field to record the date of the change.


I'm not quite sure what you are saying here:

What I would love is for the form - which cycles all the records in the database - to not cycle the fields ECR_No, Last_Edited_by, and Reason_for_Change, and instead write the records into tblDatabaseChanges and when you go back to that record via the form those fields only remain fresh & blank for each item. Without, if possible, creating a whole new dilemma with the existing audit trail info.

I have used Allen Browne's audit trail approach which can be found here

 

[irish634]

Re: Audit trail snag

Mine are almost always set up with:

1. "Enforce Referential Integrity" box checked
2. "Cascade Update Related Fields" box checked
3. Join Type #1

Also if the Record ID in tblDatabaseChanges is set to no duplicates, it likely isn't going to work the way you want it to. Make sure it's indexed, but duplicates ok.

 

[Kryst51]

I think this post is a duplicate of this other one.

 

[jzwp22]

Thanks Kryst51

 

[connie]

Since you are adding a new record each time, I cannot see why you would lose or overwrite records. Can someone be changing existing records in the audit table without adding a new one? Can someone delete an existing record? Your users should never see or have access to the audit table.

Nope, no one else is going into the tables.

I don't see a field for the equipment that the ECR pertains to (recordID?), nor do I see a date field to record the date of the change.

RecordID identifies the individual record/equipment item and is indexed w/o duplicates in tblEquipmentDatabase (the main table). ECR Number is a field on the form, but it feeds into the main table and not tblDatabaseChanges (which only records the old ECR #). DateofChange is recorded in tblDatabaseChanges by a field with a default value of =Date(). Then a query ties the two tables together. The problem may be more in my reporting than the audit trail itself, I’m not sure. But everything was going great until more than one ECR was entered for a particular item, and then this started happening (see new JPEG attached)

I'm not quite sure what you are saying here

Maybe my answer to the above ? will clarify a little – the form is based on main table, and cycles all records, which is great because we use it for editing records. But the ECR_No, Last_Edited_By, and Reason_for_Change are stored in the main table also. Since multiple ECR #’s can be entered, any record that does have an ECR related to it will show the last ECR on the record instead of being blank slate fields…I think maybe I should not have set up those 3 fields in the main table in the first place but now I’m scrambling to figure out how to fix without messing everything up. Basically one record can have many ECR’s and I should probably be setting up a sep. table for them?

1. "Enforce Referential Integrity" box checked
2. "Cascade Update Related Fields" box checked
3. Join Type #1

Thanks…before I do this…will it have any visible or predictable effects?

Kryst – I tried to click that link but it said “No valid thread, contact Administrator”

Thanks all!!

 

[Kryst51]

Kryst – I tried to click that link but it said “No valid thread, contact Administrator”

Thanks all!!

Maybe the dup was deleted. Although it had some comments on it from some people to you, so I don't know why it would be.

 

[Kryst51]

Actually it looks like the two threads were merged.

 

[jzwp22]

Basically one record can have many ECR’s and I should probably be setting up a sep. table for them?

You are describing a one-to-many relationship which should be handled with a separate table.

RecordID identifies the individual record/equipment item and is indexed w/o duplicates in tblEquipmentDatabase (the main table).

I would assume that you allow duplicates of RecordID in your tblDatabaseChanges. Is that correct?

 

[connie]

You are describing a one-to-many relationship which should be handled with a separate table. I would assume that you allow duplicates of RecordID in your tblDatabaseChanges. Is that correct?

Do you think I could incorporate it somehow into tblDatabaseChanges? What would be the best way to either do that or create a new table for it and still incorporate all that's been done already? I'm assuming an update query would be involved...

In short, it's currently like this:

tblEquipmentDatabase is the main table and stores the records with details on each equipment item, but is also holding the most recent entry for the one-to-many fields ECR_No, Last_Updated_by, and Reason_for_change. You're right, those don't belong there. The primary key tying things together is RecordID, which is indexed w/no duplicates here.

tblDatabaseChanges stores the audit trail/history of changes to those items. Within each line the old value of ECR # is recorded, as well as the old value for all other fields. Record ID is indexed w/duplicates allowed.

Keep in mind I have also not yet fixed the Relationships between these tables (currently showing Indeterminate) bc I'm wondering what, if any, effect this might have on the current database.

Thx all!

 

[connie]

There are other fields in the tables besides these, but this should paint an easier picture as well:

tblEquipmentDatabase contains:
RecordID
Item_Qty
Unit_Cost
Ext_Cost
Manufacturer
Model
ECR_No
Last_Edited_by
Reason_for_Change

tblDatabaseChanges contains:
DateofChange
RecordID
Item_Qty_Old
Unit_Cost_Old
Ext_Cost_Old
Manufacturer_Old
Model_Old
ECR_Nld
Last_Edited_by_Old
Reason_for_Change_Old
Budget_Variance

All the ones with _Old are storing the OldValue of those fields when changes are made.

Does that make sense? That was a stupid way for me to set it up in the first place including the one-to-many's discussed above.... I don't know what I was thinking

 

[jzwp22]

Thinking about this for a while, I thought that it might be nice to capture the values before the change and after the change. So for each ECR, you would generate 2 records. This would require a table to hold the basics of the ECR--who is requesting and why. Then the second table would have the two related records. Something like this

tblECRRequests
-pkECRRequestID primary key, autonumber
-fkPersonID foreign key to your table holding the people who would request a change
-fkRecordID foreign key relating to your equipment table
-dteRequest Request date
-dteCompleted

tblECRDetail (has all of the fields that are currently in your equipment table plus a couple extra)

-pkECRDetailID primary key, autonumber
-fkECRRequestID foreign key to tblECRRequest
-field to hold "before" or "after"
-your other equipment fields that may be changed

Thinking about it this way, your equipment table may hold only those fields that will never change and then you can find the most recent ECR (after values) to get the most current information.

I'm not sure if this is what you are after.

 

[connie]

Not sure how to change this thread to solved? but I've finally fixed this. Thank you so much jzwp22 (and others) for your suggestions. Because there aren't any fields that will never change (except primary key/index RecordID), I ended up doing the following:

-Changed the tblDatabaseChanges so that it recorded both the old and new values together, including those fields ECR_No, Last_Updated_by, and Reason_for_change. So each record contains one complete change.

-Created an update query to fill in the current values from the main table into the Changes table

-Fixed table relationship to One-to-Many

It all worked out. Again, thx all!

 

[jzwp22]

Glad to hear that your worked it out. Good luck with the project.