Audit Update of a Field

[Marshall Brooks]

Thanks - I was using Option Compare Database, so it probably should be case-insensitive anyway.

Update - somewhat surprising, but it does work without the LCase().

I used the WScript.Network object and it came back uppercase, even though I thought I logged in in lower case.
I compared the result to lower-case and it came back True.
Then I made a small table with my user name in lower case in one field and my name in the second field and ran ELookup with my username as lowercase and again as uppercase and both returned my real name.

 

[Marshall Brooks]

Is this a potential concern? I.e. an error occurs and the varUserName gets reset?

Did some research and answering my own question ... (Although the correct answer is "Possibly ...)

Global Variables

I am not in a position to test this but I would like someone to test if TempVars are reset on a VBA Quit command. It would seem consistent for Microsoft to reset them but, if they do, then they would not be as 'robust' as some people like to think they are. And, in general, I'm not against the...

www.access-programmers.co.uk

Check if global variable has been erased

I have a global variable of type Collection defined in a module. I know if I get an uncaught error the global variable will no longer be defined. But I am trying to check if it is undefined and if it is then call a function that sets it new and assigns its values. But I can't find a way to...

www.access-programmers.co.uk

Unhandled errors in and of themselves don't cause variables to be reset. In .accde files, variables are not reset by errors. In .accdb files, variables are reset by clicking End when the error message occurs.

I would structure my code so that the reset was non-permissive. (For example, I only allow certain users to add or delete records. I would set the code so that the add/delete button was only visible if the username matched. If the variable was reset and the varUserName was vbNull, I would have the Add/Delete Button hidden.

So for Deployment, it is probably safe to use a global variable.

For Development, there is a fairly high change of me making a typo and causing an unhandled error that I click End on and then am locked out of a lot of functions unless I close and re-open the database, so it's probably better to not use them in this case.

 

[Josef P.]

I would use a property procedure or function instead of the global variable and possibly use a private module variable or static variable if the user name is not to be determined each time.

Concept:

public Function CurrentWindowsUser() as String

    static WinUserName as String

    if len(WinUserName) = 0 then
         WinUserName = GetWindowsUserName()
    end if

   CurrentWindowsUser = WinUserName

end function

private function GetWindowsUserName() as String
   '... get Username with API call etc.
end function

 

[Marshall Brooks]

@Josef P. - That seems to have worked great!!!

@Pat Hartman - Could you please re-post the sample database from Reply #42. I don't see it as an attachment and I searched for forum for something similar and didn't see anything, but I might not have used the correct search terms. Thank you!

 

[Marshall Brooks]

@Pat Hartman - Thank you!!! I was able to modify your database to add a new table to my test copy of the BE. Worked flawlessly. It was VERY helpful to see HOW to implement the DDL changes. I have a lot more to do, but at least I understand the process now.

@jdraw - I have a somewhat simplistic question on data macros. In the audit table, you include the old value and the new value, which seems like a good idea and information I would want to know. So far, I am only needed to audit changes to date/time fields so I made those fields date/time in the new audit table. If I also wanted to audit a text field, wouldn't I get a "Type Mismatch" doing it this way? How would I work around that, other than having a date audit table and a text audit table and somehow combining the results. As I said, it isn't an issue currently, but I could see it potentially becoming one.

All - Thanks again!!!

 

[Marshall Brooks]

Long post ahead ...

@Pat Hartman - As I'll show, right now I'm using Date/Time fields in the tblAuditLog to store date/time fields. You are saying it would work fine to store these values in text fields. (It's an empty table now, I can change/rebuild it.)

@jdraw - I'm really struggling with the data macros:

First off - I'm not sure how the parameters work. The example shows a parameter name and a description, but if I add a parameter, it just asks me for name and description. Can it really just logically tell what the item should be from this? I wrote the data macro without parameters and maybe that is my issue.

Next, I'm slightly obfuscating the names, but ...

I created a new table which I'll call tblAuditlog with the following fields:
CREATE TABLE [tblAuditLog]
(
[PK] COUNTER Primary Key,
[Orig_PK] Long,
[REFERENCE] text(50),
[EVENT] text(255),
[OLD_VALUE] DateTime,
[NEW_VALUE] DateTime,
[USERNAME] text(20),
[MOD_DATE] DateTime
)

My source table I will just call tblSource and I want to create a record when field "Event 1 Complete" is changed.

I didn't use parameters and I created the data macro and changed "Event 1 Complete" for one of the records and nothing happened. No error messages and no new records in tblAuditLog.

I think what I am missing is you said it was supposed to be triggered and listening for Insert, Update, or Delete, but I don't see how I specify that.

Comments shown after ' are for explanation and not part of the interface.

My data macro (in tblSource) looks more or less like this:
If Updated("Event 1 Complete") Then
Create a record in tblAuditLog
SetField
Name tblAuditLog.Orig_PK ' Should this be just Orig_PK?
Value=[tblSource].[PrimaryKey] 'That is the field name in the source table.
SetField
Name REFERENCE
Value=[tblSource].[Reference] ' I think this should be all caps, but it didn't error.
SetField
Name EVENT
Value="Event 1 Completed" ' I want this to be text
SetField
Name OLD_VALUE
Value=[tblSource].[Event 1 Complete].[oldvalue]
SetField
Name NEW_VALUE
Value=[tblSource].[Event 1 Complete]
SetField
Name USERNAME
Value [GetUserName] ' I copied the function posted earlier to the BE module 1.
SetField
Name MOD_DATE
Value=Now()
End If

Thanks for your patience and assistance with this!

 

[Marshall Brooks]

I think I found my error - on your info tab you say "I have used After Insert, After Update, and After Delete. Notice they are highlighted." I didn't notice, but I see it now.

Let me try again!

Okay - I don't see how to make my existing named macro work for all three events. I created it with CreateNamedMacro. If I go to Edit, I can't select Events. If I don't go to edit it, I can select AfterInsert, but it gives me a new blank macro named AfterInsert.

???

Figured it out, sorry - in the blank macro, I have to tell it to run my newly created named macro. Still may have errors, but I'm making progress!!!

Still not there yet - it is supposed to run my named macro from each event, but I changed the field in the table and nothing was written to the audit table.

 

[jdraw]

Marshall,
You said:
"I'm really struggling with the data macros:"

Yes, I know the feeling. That's why I struggled and built my demo database back in 2017.
You may find some info on youtube - I know Dr. Richard Hanson created a video on data macros.
It may help with context.

Have you gone through my demo database to see how things work using table data macros and a named data macro to write data to the tblAuditLog.

An audit log/file is often used to identify what changes were made to a database, who invoked the change and when did the change occur. In other databases audit logs and/or transaction files can also be used to reconstruct business transactions.

Attached in zip file are the Data macros associated with the tStudent Table and tblAuditLog in the demo database. Again it might provide some context. You can open the files in a browser, or you can use Notepad++ and use the XML plugin Pretty Print to see the xml.

Good luck with your project.

 

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

 

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