VBA for AuditTrail Returning the Primary Index of a Record Changed

[blu_macey]

Hello experts,

Good morning!
After a lot of reading and consolidating VBA codes for audit trail finally I've made an audit trail the way I want it to be and sharing this with you guys, at your own risk, hehehe joke!
But unfortunately there still a thing I'm not fully satisfied of, that's why I'm open for your comments and suggestions to enhance this Idea.

How My Audit Trail Works

A module was made for a function named as "Changes"
Then inserted into before update event of a form where I will do the editing of the records.

Then I made a table named as Audit
Inside this table I made all the fields I needed such as:

*AuditRecordId[autonumbered]
*FormName[The name of the form for editing]
*Index[The record ID of the record being edited]
*ControlName[The Field being edited]
*DateChanged[Date change was done]
*TimeChanged[Time change was done]
*PriorInfo[for the old value of data being changed]
*NewInfo[For the new value of data changed]
*CurrentUser[The user base on log in form that was set to Global into another module]
*Reason[The reason for changing for future references]

And Here is the Function Code:

Option Compare Database

Option Explicit

Function Changes()

Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim strCtl As String
Dim strReason As String

strReason = InputBox("Explanation For Changes") 'short but brief understanding, why changes was done.
strCtl = Screen.ActiveControl.Name
strSQL = "SELECT Audit.* FROM Audit;"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then rs.MoveLast
With rs
.AddNew
rs!FormName = Screen.ActiveForm.Name
[COLOR="Red"][B]rs!Index = Screen.ActiveForm.Controls("SUP ID").Value[/B][/COLOR]
rs!ControlName = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!PriorInfo = Screen.ActiveControl.OldValue
rs!NewInfo = Screen.ActiveControl.Value
rs!CurrentUser = strUser
rs!Reason = strReason
.Update

End With

Set db = Nothing
Set rs = Nothing
End Function

Disadvantage

This audit trail function is valid only for one(1) form, due to the limitation of

Screen.ActiveForm.Controls("SUP ID").Value

where "SUP ID" is the primary key of the record being updated/Change, so if there are Five(5) tables that needs audit trail, there will be also Five(5) forms, as well as Five(5) Function Changes namely; Changes(), Changes1(),Changes2(),etc... because all the table do have their own sets of primary Key.

Question

Is there a shortcut, in such a way that the "rs!Index" will automatically return a value, equivalent to the Primary Key/Record Id of the record being updated/change, given that there are different updating forms for each table to be updated?

Additional Info

Already read some of Mr. Allen Browne's Idea on returning the value of the tables primary key, but have a hard time fitting it into my customize audit trail.

Looking forward to hear from you soon

Thank's a lot in advance, best regards.

Cheeers!
Chino

 

[pbaldy]

You could modify the function to accept the key field name as a parameter, and use that in your code.

One caution. As your audit table grows the code will slow down because this:

strSQL = "SELECT Audit.* FROM Audit;"

returns all records from the table. Either use:

strSQL = "SELECT Audit.* FROM Audit WHERE AuditRecordId = 0"

or use dbAppendOnly when opening the recordset.

 

[blu_macey]

One caution. As your audit table grows the code will slow down because this:

strSQL = "SELECT Audit.* FROM Audit;"

returns all records from the table. Either use:

strSQL = "SELECT Audit.* FROM Audit WHERE AuditRecordId = 0"

or use dbAppendOnly when opening the recordset.

Hello Sir Paul

Good morning! Thank you for your quick response, as of now my series is running at 1,028 in my audit trail table due to trial and error process of fitting the right code. I will take your advice and set it up on my code.

You could modify the function to accept the key field name as a parameter, and use that in your code.

Honestly I'm running out of idea and I don't know how to do this, but at least I'm happy to know that its possible

Many Thanks!

Cheers!
Chino

 

[pbaldy]

See if this helps:

http://www.baldyweb.com/Function.htm

You'd use it like:

rs!Index = Screen.ActiveForm.Controls(VariableName).Value

 

[blu_macey]

See if this helps:

http://www.baldyweb.com/Function.htm

You'd use it like:

rs!Index = Screen.ActiveForm.Controls(VariableName).Value

Thank you sir Paul,

Now I understand your point
More Power,

Cheers!
Chino

 

[pbaldy]

Happy to help Chino!