Any Error Handling?

The procedure is exactly as it is. I tried to do the error handling in the if blocks. I am testing on a non production database that does not have user tracking. Results so far have been positive. I will watch the video here in the next couple hours and see what needs done. The intention is to be able to pass true data changes (only for now) when applicable but also track who logs in and what forms they are navigating through which is the reason for the 'double' update passing only a description of what is happening. I am calling this from the on load, on unload, and before update events on whatever forms I want tracked.

Overall, I think you're on the right track. The basic idea is good: pass in the form, loop through the controls, compare the current value to the OldValue, and write the differences to an activity log table. That's a perfectly reasonable approach for form-level auditing.

Alex is right though: you definitely want proper error handling in here. The If blocks are good for testing conditions, but they are not a replacement for error handling. For example, if RecordIDField is misspelled, if a control doesn't support OldValue, if the recordset fails to open, or if one field has a data type issue, the whole procedure could stop. A standard On Error GoTo block would help you trap that and clean up properly.

One thing I would be careful with is frm.RecordSource for the table name. If the form is based directly on a table, that's fine. But if the form is based on a query or SQL statement, then frm.RecordSource may not be the actual table name. That's not necessarily wrong, but just be aware of what you're storing.

I would also consider separating "activity tracking" from "field change auditing." Logging that someone opened or closed a form is a different kind of event than logging that CustomerName changed from Bob to Robert. You can use the same table if you want, but I would make sure you have fields that clearly identify the type of log entry.

So yes, the general idea is acceptable and it's a good start. I would add proper error handling, make sure you test it with different control types and Null values, and be careful using it in production until you've tested it on a copy of the database. Auditing code is one of those things that seems simple until it quietly becomes the ship's black box recorder.

One thing to watch out for, especially as this evolves, is schema drift between your source and your audit table. If you add a new field to your form's underlying table but forget to account for it in your logging table or process, you can end up with unexpected failures. I run into this myself with sync routines. Everything works perfectly until I add a new field in one place and forget to update the other, and then suddenly inserts start failing. It's not obvious at first because nothing in your audit code changed, but the structure did. So just keep that in mind as you maintain this over time.

In my case, I've got a routine that copies new records from my website down to a local database for backup purposes. Every time a post is made, it gets written to a local table in addition to my regular SQL Server nightly backups. This way in case of failure, I have last night's FULL backup, plus every post and comment that was made since that time. It works great, but every once in a while I'll add a new field to the website table and forget to add it to the backup table. When that happens, the insert fails and the whole process breaks until I realize what changed. It's a good reminder that whenever your source structure changes, anything that depends on it, like auditing or sync routines, needs to be updated too.

I updated my routine with error handling to notify me immediately if this happens. It's on my future TO DO list to have the database actually insert that new field for me. That list is centuries long. LOL.

Richard I appreciate the deeper input. As of now, all information is stored in text fields so in theory the 'table name' saved could be OrderQ or something because those do exist. Assuming it works the biggest issue I can foresee (I hope) is that making sure the data that may need changed back is correct but for now that is exclusively on me anyway. I did end up tweaking the code to store the types in two different tables because I already had the activity tracking in place so I just added the data tracking to a new table and restructured things a bit to be similar between the two but decided activity logs could be cleared pretty regularly because they are more for very recent 'Who is where?' situations. I can provide my current code if anyone wants to see the change. I did watch that error handling video and it made sense but with that being the only frame of reference it is difficult for me to understand what I may need to trap specifically. I've tied it in to a copy of the production database and asked my guy to run them side by side for a bit to see if any weird errors happen and happily discovered that it records new records being added as well.