OK, to start with, Access by itself will never be 100.000% compliant, at least in any version of the product I have seen, since you (as Admin) can get into a context where events and underlying code do not exist.
However, if you can prevent the users from seeing the database window, and if you use workgroup (or other equivalent) security logins, you can accomplish most of what you want through code.
In fact, you are going to do a lot of coding. I believe I understand the requirement. I'm in Dept. of Defense, USA, so I know a little about audit logs. Don't know if you use the same terms for FDA, but DoD calls audit logging part of their "C2" security requirements.
First, you need an audit log table. You will need, as a minimum, a [datetime] field, a [username] field, a [tablename] field, a [fieldname] field, a [from] field, and a [to] field. You might also want a [Comments] field. Here's a tip I learned when I did something like this: Don't make the primary key equal to the [datetime] field. In fact, it is not required to have a primary key in this audit table. You won't use it in a way that requires linking in that manner.
You can make an index on the date if you wish, but allow duplicates. Today's machines are fast enough that if you log two edits in a row, you will find it possible to have the same time in both events. The internal clock resolution isn't fine enough to prevent duplicates. If you really, REALLY worry about the security requirements and getting events in absolute event order, use a separate autonumber field.
For tablename & fieldname, you can either (a) use the actual names or (b) use the indexes from the tabledefs and tabledefs
.fields collections. (Property: tabledefs("myTable").Index) But if you ever change these definitions, the indexes might be considered "unstable" in a data longevity sense. I would use the actual names. That's just me. You know better than I do how stable the names will be long-term.
When logging the From/To values, I would log the CStr of the values. If you ever had to restore from the audit, you would have to look up the field to get its data type, then do a CDate, CInt, CDbl, etc.
Audit tables tend to grow. Proper security considerations require that you set up a procedure to flush the audit table to an external file and empty the table. (To be self-consistent, make an audit event before you flush and another one after.) Remember that [Comment] field I said you might need? The audit flush operation is one case where it might help. Anyway, you do the flush because you don't want the audit table sitting in the same place as the thing you are auditing for any longer than required.
In your application, your dearest friend is going to be each form's BeforeUpdate event. Here, you can put code to scan the controls of the form to see what changed before the update goes through. You might be able to make some of this generic enough to write it in a general module, then just pass the name of the form. Or, you could cut/paste part of this. What I did was make the actual logging code a subroutine and cut/pasted the loop into each form. Then, I passed the control and the recordset into the logger. The logger can do CurrentUser and Now just as easily as the main. The linkage I used passed in a partly formatted string for the table info, and in my case, I did not need field level logging, so my case was easier than yours.
Basically, here is the skeleton of your routine. Not actual code, just concepts.
Open the audit table recordset in a way that allows appending.
Start a loop over all controls in Me.Controls. For each control, test its .ControlType property. If it is a type that has values, compare the .OldValue to the .Value property. If they are equal, forget it. (Types you can easily skip include but are not limited to lines, rectangles, labels, index-tabs, and the individual buttons of a radio-button group - but not the enclosing group, which DOES have an old and new value.) You might also make life easier by skipping fields that aren't enabled or that are locked. But that is your call. And if locked field Y can be changed by updating combo box X, then the "locked field" exception doesn't apply.
If the control could be updated, you can find the Control.Name, .ControlSource, .RowSource etc. If the source is an elementary field name from a table or query, you can find the user (CurrentUser), date&time (Now), the table/query name, field name, old value, and new value. (You could also put the name of the form in the [comments], particularly if the same field can be modified from more than one location. Do a .AddNew on the audit table's recordset, populate the fields, and update the RS, then continue your loop over all controls. When you hit the end of the loop, you are done. Now let the update happen.
(Of course, if there is a pre-update test that might block the operation, do that blocking test first.)
Now, the part that gets ugly. You can NEVER use update, append, make-table, or delete queries directly. ONLY select queries. All other operations, to be properly auditied, must be done through VBA if you want record-level auditing. This is why most folks don't try to implement this sort of thing completely in Access. Yes, you can do it. But at some point you would do better to find an ODBC link to a database manager that has auditing built in as an automatic thing. Unlike Access, that gives you all the tools you need but none of the pre-built infrastructure.
Good luck! I don't envy you the work you have ahead of you.
