Audit columns: event procedures vs. modules/functions

[Marmalade]

Hi, folks,

I have added audit columns (created_by, created_date, last_edited_by, last_edited_date) to all of the tables in my database. By the following code (taken from a FAQ on this site) to the BeforeInsert and BeforeUpdate event properties on my forms, I am now able to populate the audit fields automatically whenever a user adds or edits a record:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!Created_By = CurrentUser()
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!Last_Updated_By = CurrentUser()
Me!Last_Updated_Date = Now()
End Sub

Unfortunately, I now have to add this code to the event properties of every form in my database.

Is there a way to encapsulate this code as a module, or as some kind of function, that can be called from every form?

Thanks,

M

 

[WMcGregor]

You can but in this case you won't save much code. Try this if you want, it should work... In a module create the following Functions:

Function Plug_Create_Date(frm As Form)
frm!Created_by = CurrentUser()
End Function

Function Plug_Update_Date(frm As Form)
frm!Last_Updated_by = CurrentUser()
frm!Last_Updated_Date = Now()
End Function

You can then invoke these from your forms using:

Plug_Create_Date Me
or
Plug_Update_Date Me

 

[Marmalade]

Thank you very much.

Please forgive the following questions, but I'm just learning my way around VBA. What kind of argument is frm As Form? And how exactly can I invoke those functions once I've created the module? Can I simply insert Plug_Create_Date Me in the BeforeInsert field, or do I need to create an event procedure using Plug_Create_Date Me?

This may be sort of off-topic, but I initially tried populating the audit fields using AfterUpdate, and got strange results. Whenever I tried to save an edited record, for example, Access told me that it couldn't do it; I'd close the form, and the results would be saved anyhow. Would you happen to know why?

 

[MichaelRed]

Atogether, this is (IMHO) a poor approach. It adds to the record size and complexity of processing. A 'transaction log' is MUCH easier to implement and records not just the most recent RECORD change, but records the individual field modifications along with the date / time and user id for every change.

see faq181-291 for a sample of the approach.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[WMcGregor]

You should be able to replace your Me!Created_By = CurrentUser() in the Before Insert event with the statement Plug_Create_Date Me and get the same results. The frm as Form parameter is allowing you to pass the form reference to the function similar to using a Me! in the actual form itself.

 

[Marmalade]

Thank you both *very* much.

WM. - I did as you recommended, and my audit tables are now purring away. Thanks for explaining the frm As Form parameter, too; I'm trying to learn as much as I can about VB/VBA as I blunder through this project.

Michael - I'm going to try the code in the FAQ on a test copy of my database. Am I correct in assuming that I should enter the public functions in a standard module (or should that be two standard modules - one for each function?), and that the Record ID is in fact the Record Source for a given form?

 

[Marmalade]

Michael - another question - should the references to "Qi" in Qi_Num and QiVal in the basAddHist code also be replaced with the unique record ID for a given form? If so, does this mean that I'll have to produce separate modules - and separate History tables - for each individual form for which I wish to establish a transaction log?

 

[MichaelRed]

The module for each FORM needs to be in the code for the specific form. It is POSSIBLE to make this a common module, but it is somewhat of a hassle (at least for me), as whenever the form looses focus, the dirty property is re-set.


re the references to "QI", you are generally "correct". You would not need seperate history tables, but you MAY want to add the "formname" to the history table, to be able to track where the change was initiated.

The Qi vars are the Unique identifiers, and need to be "replaced" by your variable names if you choose different ones. MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over