Audit Update of a Field

[Marshall Brooks]

@jdraw - Thank you - I'll look at your example later, but I was using your example database from the linked thread above: Reply #41, and thought I understood them.

I have a quick question:

I'm not sure I understand the concept of the AfterInsert, AfterUpdate, and AfterDelete "triggers". If I understand correctly, I think I only need to look at AfterUpdate. If AfterInsert is when a new record is created, the fields I am tracking would be intially and for some time afterward blank, so I don't care about tracking changes. If AfterDelete is when a record is deleted, I don't care either. If the deletion was intentional and correct, the record is gone and I don't care about the tracking history. If the deletion was incorrect, I will want to restore ALL the fields from the backup and therefore also don't care about the audit history - although it could be useful to verify when and by whom the record was deleted.

Next week, if I can't figure it out from your attachment, I'll try to work incrementally - i.e. I'll use a new macro with an action such as "Display a MsgBox "DM Fired" for the after update event. Then move that to show a msgBox if the specific field changed, then write text to the tblAuditLog Field if the field changed.

If I'm still stuck, I'll create a demo DB and post that.

Thanks again!!!

 

[jdraw]

It's difficult to advise specifically, because your real requirement seems illusive/elusive( at least to me).
This "I don't care about tracking changes." and "I will want to restore ALL the fields from the backup and therefore also don't care about the audit history - although it could be useful to verify when and by whom the record was deleted"
raise questions about your project.

Perhaps you can list your requirement(s) with respect "changed data values" so readers can better understand your situation and approach as you understand them now. That way nobody has to guess/interpret what has been written so far. That may also get you a more focused line of attack.

?How will you know what fields are in error in order to replace them from backup?
?If you physically delete records, how will you identify such missing records?

Many people, me included, would advise you to NOT physically delete/remove a record from a table. The alternative is to design your set up to use and respect a boolean flag/indicator indicating "Deleted Record".

A tblAuditLog can record all change activity (Create, Update, Delete) at the record and field level. The need for and use of such a table depends on your requirement.

If you "only" need to know:
-when a record was created, or
-when a record was changed, or
-when a record was deleted, and who made the change,
then add fields to your table/record structure and populate same in your change code logic.

Note: You will have to use a boolean flag type approach if you want to know who deleted a record and when.

Additional fields in record:
CreatedBy
CreateDate
LastModifiedBy
LastModifiedDate

You can design/run Reports/Queries to review your changes or whatever as needed.

Choice of recording any information really depends on your requirement.

 

[Marshall Brooks]

I have the update macros working. Not sure if it was a requirement, but the big issue for me what I needed to close and re-open the tblAuditResults to view the changes.

 

[jdraw]

So, current status is --you have table update data macros working, right?
Can you show us a few records from you tblAuditResults?

Can you restate your requirement regarding only needing to record Updates?

 

[The_Doc_Man]

Working only via bound forms is a bit one-sided. Table entries can also be made via action queries, record sets and standard imports. Doesn't something like that happen to you?

Just noticed this question, but I let it pass earlier. No, things like that DON'T happen in my databases unless the user clicks a button that exists for that feature. My users don't see the list of action queries, can't run code that would open record sets, and the import templates aren't available for editing. If someone were to make a secondary front-end to do those things, that person would do it only until we found out about it, at which time the department manager would bring that person a big box to clean out his desk. If the security IT manager didn't beat him to it...

With the Navy databases, everything and I definitely mean EVERYTHING was locked down. Was that stuff possible? Yes, with appropriate controls. Was it unaudited? It DEFINITELY was audited.

 

[Marshall Brooks]

So, current status is --you have table update data macros working, right?

Not completely, but I'm still testing and making progress.

When I finish testing, I'll upload an example database with working and non-working examples.

One thing I have found is there is no error-reporting. I.e. if you have a DM working to create a record with five fields and you make a typo when adding an additional field - NOTHING happens. The five previously working fields aren't updated, you don't see an error message.

Can you restate your requirement regarding only needing to record Updates?

Essentially, we have 11 "milestone" fields that we want to track as they are changed/updated. That is the "requirement" for this project. Really, we don't care about added or deleted records.

We have had issues in the past where a record went "missing" and or got copied to a new record that got created, and it could be useful to know who was using the database when this happens.

Table entries can also be made via action queries, record sets and standard imports.

That is another reason I wanted to use Data Macros for this. These are date fields, but typically the user doesn't directly input the data, they click in the field and it calls up a "datepicker" and that result updates the field. I think that is considered updating the field via VBA and doesn't trigger many of the update events.

I'll post the demo after further testing.

 

[Pat Hartman]

Essentially, we have 11 "milestone" fields that we want to track as they are changed/updated.

Sounds like a repeating group that should be a child table instead. Do you have a plan for adding a 12th milestone?

 

[Marshall Brooks]

It's not a repeating group and they do want to add additional milestones.

 

[Marshall Brooks]

Test Database attached. There are DM's named ...works and ...fails. For Update, the only thing that is not working is the old value, but I tried to follow your (@jdraw ) example.

I tried using the "Create Record" steps without the "If Event 1 Updated" clause for deleted records and that did not work either.

Thanks for looking into this!!!

 

[Josef P.]

the only thing that is not working is the old value

SetField
Name: tblAuditLog.OLD_VALUE
Value: [Old].[Event 1 Complete]

 

[Marshall Brooks]

@Josef P. - THANK YOU!!!

 

[jdraw]

Marshall,
Glad you have it working. You are using a named Data macro.
I did a few tests after clearing test data.
Here are the auditlog records for a few test records I added and edited in tblSource.

tblAuditLog tblAuditLog

PK	Orig_PK	REFERENCE	EVENT	OLD_VALUE	NEW_VALUE	USERNAME	MOD_DATE
3​	2​	kl-78922	Event 1 Complete	02-Mar-23	02-Jun-23	jp	08-Jan-24 6:26:29 PM​
4​	3​	jk-9533	Event 1 Complete	19-Sep-23	19-Oct-23	jp	08-Jan-24 6:28:13 PM​
5​	4​	BG-922	Event 1 Complete		03-Jan-24	jp	08-Jan-24 6:29:14 PM​
6​	5​	BG-9223	Event 1 Complete		02-Jan-24	jp	08-Jan-24 6:29:33 PM​
7​	2​	kl-78922	Event 1 Complete	02-Jun-23	02-Jun-24	jp	08-Jan-24 6:30:15 PM​
8​	5​	BG-9223	Event 1 Complete	02-Jan-24	02-Mar-24	jp	08-Jan-24 6:30:27 PM​
9​	6​	BHT-12	Event 1 Complete	30-Jan-24	01-Jul-24	jp	08-Jan-24 6:30:47 PM​

Note: If you have errors when coding/writing/testing your data macros, those errors will be written to system table USysApplicationlog. You have to set Navigation options to show System tables to see this table.

 

[Marshall Brooks]

@jdraw - Thanks - I did see that new table, but wasn't sure what it did. Is it safe to delete it now?

Also - what would it take to have DM's that checked for added and or deleted records. I would want it to use the same format and write to the same table, but not worry about the Event 1 Complete field changing and only create ONE new record in the table with an Event name of "Record Deleted" or "Record Added" and the UserName and Mod_Date.

I tried to set this up myself and didn't get it to work.

Thank you again!

 

[jdraw]

You can delete UsysApplicationlog once you have finished testing. It does give info on errors that can be "helpful".
If you look at my sample database in BestUse of DataMacros, you'll see that I used parameters in data macros and used the Named data macro to write the audit record. You have instructed the AfterUpdate data macro on tblSource to run your named data macro.
I'll be out most of the day and we're expecting lots of snow later today and overnight.

 

[Marshall Brooks]

Understood. I was wanting to create a different named data macro to call from the AfterDelete and AfterInsert DM's, but it didn't work, but I'll do more testing.

Stay safe with the poor weather!

 

[Marshall Brooks]

Got it working. For Deleted records, I had to transfer the [Old].value to the tblAuditLog, or it created blank entries.

Thank you again for showing me how to create these.

 

[Pat Hartman]

they do want to add additional milestones.

Today

 

[Marshall Brooks]

Okay, I'm back to things I don't know how to do ...

First, I have the DM populating the tblAuditLog.

I want to display real names in the Audit Table so I created a query in the FE based on the tblAuditLog. I have another table which I'll call tblUsers with a column I'll call RealName and a column I'll call UserName.

MsgBox Nz(ELookup("[RealName]", "[tblUsers]", "[UserName] = '" & GetUserName & "'"), GetUserName)

will display the real name if it exists or the username if it does not exist.

I want to add this as a column in my Query using the result in column UserName and adding a New Column for RealName.

Then, I want a button on the form to display the auditTable Query. (I know how to do this, I want to filter it to only results that match the current record). I think I know how to do that, I'll post back if I have problems with it.

Then, we want to save the displayed info to a PDF File. I'm not sure if I can add a button on the form to print the form to a PDF, or if I have to set up a report structure to do this, and I'm not sure how to do either one.

Thanks again!

 

[Minty]

Don't store redundant information. Rather than using a lookup, you can link the UserName in your Audit table to your users table in the query to display the result, don't store both bits of data. Use a left join in case there isn't a UserName for some reason.

Use a very similar syntax to create the calculated field in the query:

DisplayName : Nz([RealName],[Username])

The report should be simple once you have the core data.

 

[Marshall Brooks]

@Minty - Sounds good, but I'm not following you! Do I need to add the tblUsers as a reference to the qryAuditTable? How do I add the additional column?

Stuck again ...

I have the pop-up form displaying the query results. I want to only display results that match the currently selected record in one of my bound forms.

I tried adding the following lines in my Form_Load() Event:
Me.Filter = "Orig_PK = """ & Forms![FormName].[PrimaryKey] & """"
Me.FilterOn = True

I'm getting an error "Microsoft Access cannot find the referenced form "FormName", even though I use the same expression in module code ...