Audit Trail field name value - Rename to something for user friendly (1 Viewer)

[RickHunter84]

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]

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]

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]

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]

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]

@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]

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]

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

 

[The_Doc_Man]

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

 

[MajP]

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.

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	FieldDescription	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.

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]

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

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.

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

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]

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]

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

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.

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

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]

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]

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

 

[MajP]

@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.