Audit Trail field name value - Rename to something for user friendly

RickHunter84 · Aug 20, 2021

Hello friends,

I'm trying to come up with a simple solution to what I think it should be simple problem

, but of course it cant be that simple...

So I have an audit table with the usual fields:

AuditID
TimeStamp
UserName
FormName
Action
RecordID
FieldName
OldValue
NewValue

The information is saved in the table without any problems, but the "problem" I'm having is more of a aesthetic. Every time the control source is recorded, it grabs the name of field of the bound control, which could look sometimes funny. Example : "PurchaseOrderDate", I'd like to have it change to "PO date :" - I tried adding IIF statement in the Query and it works, but there will be too many IIF to handle this "problem"

Any thoughts on how to handle this "problem"

thank you in advance,

Rick

June7 · Aug 20, 2021

Why would you want to obscure the data with alias values?

Could have a 'mapping' table then use DLookup() to pull alias, no IIf.

DLookup("AliasFieldName", "tblMap", "FieldName='" & Me!FieldName & "'")

But I suppose this would require mapping EVERY field in database.

JonXL · Aug 20, 2021

A properly-built database shouldn't really have that many fields, should it?

But I agree with using a mapping table. Only I wouldn't go with having DLookup() to fill the table with friendly names. Users don't often go to audit tables and that would slow down data entry.

Instead, store the underlying field names and use a JOIN query in the audit form's/report's record source to fetch the friendly names. This means making one mapping table and editing one audit query. You'll also have your underlying audit records storing the real field names so it will make sense to you.

RickHunter84 · Aug 20, 2021

JonXL said:
A properly-built database shouldn't really have that many fields, should it?

But I agree with using a mapping table. Only I wouldn't go with having DLookup() to fill the table with friendly names. Users don't often go to audit tables and that would slow down data entry.

Instead, store the underlying field names and use a JOIN query in the audit form's/report's record source to fetch the friendly names. This means making one mapping table and editing one audit query. You'll also have your underlying audit records storing the real field names so it will make sense to you.

I understand and agree with some of your points. However, the first comment "A properly-built database shouldn't really have that many fields" - this definitely doesn't apply to every project.

Also, in my industry experience, the audit trail of new/edit/deleted is used quiet a lot. So in my case, it needs to be easy to understand.

Thank you for your proposed way of dealing with the "issue"

regards,

Rick

NauticalGent · Aug 20, 2021

I encountered something similar with a validation routine. I wanted to inform a user that a field could not be blank but the control's name (txtPO_Num) isnt really suitable for a user. However, the label (Purchase Order Number) was.

So instead I use the associated control's label: Me.ControlName.Controls(0).Caption

jdraw · Aug 20, 2021

@Rick,
Have you searched this and other forums for Audit trail with Access?

You may find some of these helpful:

audit trail

Hi all can you assist with this problem please , I have a database this a main form [mainfrm]and two subforms [subform]and [ work details], when I open the main form I keep the subforms hidden until a button is clicked , the subforms are connected via a one to many relationship, all works great...

www.access-programmers.co.uk

Best use of Macros

I would be interested in a discussion as to when it's best to use a macro vs. VB program.

www.access-programmers.co.uk

RickHunter84 · Aug 20, 2021

NauticalGent said:
I encountered something similar with a validation routine. I wanted to inform a user that a field could not be blank but the control's name (txtPO_Num) isnt really suitable for a user. However, the label (Purchase Order Number) was.

So instead I use the associated control's label: Me.ControlName.Controls(0).Caption

Man! you nail it!!! there was not need to do more coding but just replace one line in the code...the simplest solution!

Have a nice day!

Rick

NauticalGent · Aug 20, 2021

Glad you found it useful, best of luck with your project!

The_Doc_Man · Aug 20, 2021

Sometimes, simpler is better.
Way to go, NG!

MajP · Aug 20, 2021

Keep in mind that this only works if there is an associated label. For me this would probably be a 50% solution since I disassociate many of my labels. You will need to have error checking if no associated label or that code fails. I would either go with just an Alias table or first see if an associated label exists and if not use the alias table.

I would build my alias table using code. This will work with any number of tables and fields. If the field has a caption then save that as the descriptive field name. Once you make the table you can make some update queries to quickly clean up the Alias (DescriptiveFieldName).

So to test I imported (linked) the fields from a large DB, and build my Alias table
tblFieldAlias tblFieldAlias

FieldAliasID	TableName	FieldName	DescriptiveFieldName	FieldDescription	FieldType	HasCaption
585					0	No

I saved a couple other values that may be helpful.

Then I ran the code.

Code:

Public Sub FillAliasTable()
  Dim tdf As TableDef
  Dim RS As DAO.Recordset
  Dim Fld As DAO.Field
  Dim strSql As String
  Dim tableName As String
  Dim FieldName As String
  Dim DescriptiveFieldName As String
  Dim FieldDescription As String
  Dim FieldType As Long
  Dim HasCaption As Boolean
  Const TableAlias = "tblFieldAlias"
   For Each tdf In CurrentDb.TableDefs
    tableName = tdf.Name
    If tableName <> TableAlias And Left(tableName, 4) <> "Msys" Then
        Set RS = CurrentDb.OpenRecordset(tableName)
        For Each Fld In RS.Fields
          FieldName = "'" & Fld.Name & "'"
          FieldType = Fld.Type
          If HasProperty(Fld, "Caption") Then
            DescriptiveFieldName = "'" & Fld.Properties("Caption") & "'"
            HasCaption = True
          Else
            HasCaption = False
            DescriptiveFieldName = FieldName
          End If
          If HasProperty(Fld, "Description") Then
            FieldDescription = "'" & Fld.Properties("Description") & "'"
            Debug.Print FieldDescription
          Else
            FieldDescription = "NULL"
          End If
          strSql = "INSERT INTO " & TableAlias & "(TableName, FieldName, DescriptiveFieldName, FieldDescription, FieldType, HasCaption) VALUES ('" & tableName & "', " & FieldName & ", " & DescriptiveFieldName & ", " & FieldDescription & ", " & FieldType & ", " & HasCaption & ")"
          Debug.Print strSql
          CurrentDb.Execute strSql
        Next Fld
    End If
  Next tdf
 
End Sub

Public Function HasProperty(Fld As DAO.Field, PropertyName As String) As Boolean
  Dim prp As DAO.Property
  For Each prp In Fld.Properties
    If prp.Name = PropertyName Then
      HasProperty = True
      Exit Function
    End If
  Next prp
End Function

This builds me something like this
tblFieldAlias tblFieldAlias

FieldAliasID	TableName	FieldName	DescriptiveFieldName	FieldType	HasCaption
630	tblGuests	GU_ID	GU_ID	4	No
631	tblGuests	GU_Created_Date	GU_Created_Date	8	No
632	tblGuests	GU_Created_User	GU_Created_User	10	No
633	tblGuests	GU_Updated_Date	GU_Updated_Date	8	No
634	tblGuests	GU_Updated_User	GU_Updated_User	10	No
635	tblGuests	OR_ID	OR_ID	4	No
636	tblGuests	GI_ID	GI_ID	4	Yes
637	tblGuests	GT_ID	GT_ID	4	Yes
638	tblGuests	GU_Name_Last	Last Name	10	Yes
639	tblGuests	GU_Name_First	First Name	10	Yes
640	tblGuests	GU_Title	Title	10	Yes
641	tblGuests	GU_Email_Work	Email_Work	10	Yes

You can see if it has a Caption I use the caption.
I use a lot of CamelCase and Underscores. So I use this to remove the Underscores.

Code:

Public Sub ReplaceUnderscores()
  Dim strSql As String
  Const TableAlias = "tblFieldAlias"
  strSql = "Update " & TableAlias & " Set DescriptiveFieldName = replace([descriptiveFieldName],'_',' ')"
  CurrentDb.Execute strSql
End Sub

So then I would have something like
tblFieldAlias tblFieldAlias

TableName	FieldName	DescriptiveFieldName
tblGuests	GU_ID	GU ID
tblGuests	GU_Created_Date	GU Created Date
tblGuests	GU_Created_User	GU Created User
tblGuests	GU_Updated_Date	GU Updated Date
tblGuests	GU_Updated_User	GU Updated User
tblGuests	OR_ID	OR ID
tblGuests	GI_ID	GI ID
tblGuests	GT_ID	GT ID
tblGuests	GU_Name_Last	Last Name
tblGuests	GU_Name_First	First Name
tblGuests	GU_Title	Title
tblGuests	GU_Email_Work	Email Work

MajP · Aug 20, 2021

Part 2.
Then I also use a lot of CamelCase.
First I capitlalize the first letter in each word

Code:

Public Sub CapitalizeFirst()
  Dim strSql As String
  Const TableAlias = "tblFieldAlias"
  strSql = "Update " & TableAlias & " Set DescriptiveFieldName = Ucase(Left([descriptiveFieldName],1)) & Mid([descriptiveFieldName],2)"
  CurrentDb.Execute strSql
End Sub

To find CamelCase I use RegExp. If I was smarter on RegExp I probably could do a find and Replace. LastName to Last Name. However, I just use it in a query to find which ones have Camel Case and then edit.

Code:

Public Function HasCamelCase(StringToCheck As Variant) As Boolean
    Dim Pattern As String
    Dim re As Object
 
    If Not IsNull(StringToCheck) Then
       ' Pattern = "^[0-9][A-Z][0-9]{3}[A-Z][0-9]{4}[A-Z]$|^[0-9][A-Z][0-9]{3}[A-Z][0-9]{4}$"
        'Lower camel case
        Pattern = "^[a-z]+((\d)|([A-Z0-9][a-z0-9]+))*([A-Z])"
        Set re = CreateObject("VBScript.RegExp")
        re.Pattern = Pattern
        re.Global = False
        're.IgnoreCase = True
        HasCamelCase = re.test(StringToCheck)
        If HasCamelCase Then Exit Function
        'Check upper camelCase
        Pattern = "^([A-Z][a-z0-9]+)((\d)|([A-Z0-9][a-z0-9]+))*([A-Z])"
        re.Pattern = Pattern
        HasCamelCase = re.test(StringToCheck)
    End If
End Function

In a query

Code:

SELECT
 tblFieldAlias.FieldName,
  tblFieldAlias.DescriptiveFieldName
FROM
 tblFieldAlias
WHERE hasCamelCase([DescriptiveFieldName])=True

So then I see what I have to Edit
qryHasCamelCase qryHasCamelCase

FieldName	DescriptiveFieldName
reportDescription	ReportDescription
UseCode	UseCode
reportSort	ReportSort
DefaultReportPath	DefaultReportPath
EmailDistrib	EmailDistrib
GU_LinkedIn	LinkedIn
lookupID	LookupID
LookupValue	LookupValue
LookupDisplay	LookupDisplay
LookupType	LookupType
LookupSort	LookupSort
reportID	ReportID
reportName	ReportName

Then it is fast to just add the spaces
Giving me
qryHasCamelCase qryHasCamelCase

FieldName	DescriptiveFieldName
reportDescription	Report Description
UseCode	Use Code
reportSort	Report Sort
DefaultReportPath	Default Report Path
EmailDistrib	Email Distrib
GU_LinkedIn	LinkedIn
lookupID	Lookup ID
LookupValue	Lookup Value
LookupDisplay	Lookup Display
LookupType	Lookup Type
LookupSort	Lookup Sort
reportID	Report ID
reportName	Report Name

So you can build an Alias table rather quickly no matter how big the DB. You can do some other update queries to clean up further. In this case I also prefixed the field names with table prefix. I would just do another series of updates.

Pat Hartman · Aug 20, 2021

Any thoughts on how to handle this "problem"

You are making a problem where none exists. If you use CamelCase, use MajP's code to split into words. If you use the_underscore, you can use the Split() function. This also requires that you use standard and meaningful abbreviations. Not everyone does. NG's suggestion to use attached labels will probably get you better names. In either case, log the actual name also.

RickHunter84 · Aug 21, 2021

MajP said:
Part 2.
Then I also use a lot of CamelCase.
First I capitlalize the first letter in each word

Code:

Public Sub CapitalizeFirst() Dim strSql As String Const TableAlias = "tblFieldAlias" strSql = "Update " & TableAlias & " Set DescriptiveFieldName = Ucase(Left([descriptiveFieldName],1)) & Mid([descriptiveFieldName],2)" CurrentDb.Execute strSql End Sub

To find CamelCase I use RegExp. If I was smarter on RegExp I probably could do a find and Replace. LastName to Last Name. However, I just use it in a query to find which ones have Camel Case and then edit.

Code:

Public Function HasCamelCase(StringToCheck As Variant) As Boolean Dim Pattern As String Dim re As Object If Not IsNull(StringToCheck) Then ' Pattern = "^[0-9][A-Z][0-9]{3}[A-Z][0-9]{4}[A-Z]$|^[0-9][A-Z][0-9]{3}[A-Z][0-9]{4}$" 'Lower camel case Pattern = "^[a-z]+((\d)|([A-Z0-9][a-z0-9]+))*([A-Z])" Set re = CreateObject("VBScript.RegExp") re.Pattern = Pattern re.Global = False 're.IgnoreCase = True HasCamelCase = re.test(StringToCheck) If HasCamelCase Then Exit Function 'Check upper camelCase Pattern = "^([A-Z][a-z0-9]+)((\d)|([A-Z0-9][a-z0-9]+))*([A-Z])" re.Pattern = Pattern HasCamelCase = re.test(StringToCheck) End If End Function

In a query

Code:

SELECT tblFieldAlias.FieldName, tblFieldAlias.DescriptiveFieldName FROM tblFieldAlias WHERE hasCamelCase([DescriptiveFieldName])=True

So then I see what I have to Edit
qryHasCamelCase qryHasCamelCase

FieldName DescriptiveFieldName
reportDescription ReportDescription
UseCode UseCode
reportSort ReportSort
DefaultReportPath DefaultReportPath
EmailDistrib EmailDistrib
GU_LinkedIn LinkedIn
lookupID LookupID
LookupValue LookupValue
LookupDisplay LookupDisplay
LookupType LookupType
LookupSort LookupSort
reportID ReportID
reportName ReportName

Then it is fast to just add the spaces
Giving me
qryHasCamelCase qryHasCamelCase

FieldName DescriptiveFieldName
reportDescription Report Description
UseCode Use Code
reportSort Report Sort
DefaultReportPath Default Report Path
EmailDistrib Email Distrib
GU_LinkedIn LinkedIn
lookupID Lookup ID
LookupValue Lookup Value
LookupDisplay Lookup Display
LookupType Lookup Type
LookupSort Lookup Sort
reportID Report ID
reportName Report Name

So you can build an Alias table rather quickly no matter how big the DB. You can do some other update queries to clean up further. In this case I also prefixed the field names with table prefix. I would just do another series of updates.

Thank you so much for your examples! great insight on how to handle this situation.

have a good weekend!

Rick

RickHunter84 · Aug 21, 2021

Pat Hartman said:
You are making a problem where none exists. If you use CamelCase, use MajP's code to split into words. If you use the_underscore, you can use the Split() function. This also requires that you use standard and meaningful abbreviations. Not everyone does. NG's suggestion to use attached labels will probably get you better names. In either case, log the actual name also.

As I said in the initial thread it was a pure aesthetic issue, I called it a "problem". Thank you for your response

Rick

NauticalGent · Aug 21, 2021

MajP said:
Keep in mind that this only works if there is an associated label.

MajP · Aug 21, 2021

@RickHunter84
I think this is more than just aesthetic and could be very useful.

As I said in the initial thread it was a pure aesthetic issue, I called it a "problem".

That is, as long as you are storing both the real names as well as your aliases. This could be especially true for those who do not use good naming conventions.
(However your example seems to be the opposite where you have a clear naming convention ex. PurchaseOrderDate)
But often people have weak naming conventions or very shorthand in the names. In that case it may be hard for anyone who did not build the database to know what changed. Even in my example I see
GU_Title but that may not mean anything. I had to open the database and figure out that is "Guest Professional Title / Position".
So using some of these approaches you now would have quick context of the real field name but also what the data represents.

Audit Trail field name value - Rename to something for user friendly

RickHunter84

Registered User.

June7

AWF VIP

JonXL

Active member

RickHunter84

Registered User.

NauticalGent

Ignore List Poster Boy

jdraw

Super Moderator

audit trail

Best use of Macros

RickHunter84

Registered User.

NauticalGent

Ignore List Poster Boy

The_Doc_Man

Immoderate Moderator

MajP

You've got your good things, and you've got mine.

MajP

You've got your good things, and you've got mine.

Pat Hartman

Super Moderator

RickHunter84

Registered User.

RickHunter84

Registered User.

NauticalGent

Ignore List Poster Boy

MajP

You've got your good things, and you've got mine.

Similar threads

Users who are viewing this thread