AuditTrail Tracking Problem, Access 2010

[accote]

Hello, and thanks for any helpful input.


I have the Module below in my DB, it tracks any changes made within all my forms in an table called Audit.
It works great, except for one problem I'm having:
I have 2 tables that are in a relationship (Institutions & Contacts) where one Institution can have many Contacts.
In my "Contacts" Form I have a Combo Box that pulls the value from the Institutions Table
(Row Source: Institution, Row Source Type: Table/Query, Column Count: 2 [Institution ID, Institution Name], Column Widths: 0";2.5")
Here is the problem, the Audit Table shows only the Institution ID in the Before & After Value, and I need it to show the Institution Name.
Does anyone know how to fix the code below to accomplish that?
I tried everything within the forms and related tables, but nothing works. I can't change the related table data type to text, data type has
to be "Number" otherwise the DB does not work with SharePoint.

Option Compare Database

Const cDQ As String = """"

Sub AuditTrail(frm As Form, RecordID As Control)
'Track changes to data.
'recordid identifies the pk field's corresponding
'control in frm, in order to id record.
Dim ctl As Control
Dim varBefore As Variant
Dim varAfter As Variant
Dim strControlName As String
Dim strSQL As String
On Error GoTo ErrHandler
'Get changed values.
For Each ctl In frm.Controls
With ctl
'Avoid labels and other controls with Value property.
If .ControlType = acComboBox Then
If  (.Value <> .OldValue) Or ((Not IsNull(.OldValue) And  IsNull(.Value))) Or ((IsNull(.OldValue) And Not IsNull(.Value))) Then
varBefore = .OldValue
varAfter = .Value
strControlName = .Name
'Build INSERT INTO statement.
strSQL = "INSERT INTO " _
& "Audit (EditDate, User, RecordID, SourceTable, " _
& " SourceField, BeforeValue, AfterValue) " _
& "VALUES (Now()," _
& cDQ & Environ("username") & cDQ & ", " _
& cDQ & RecordID.Value & cDQ & ", " _
& cDQ & frm.RecordSource & cDQ & ", " _
& cDQ & .Name & cDQ & ", " _
& cDQ & varBefore & cDQ & ", " _
& cDQ & varAfter & cDQ & ")"
'View evaluated statement in Immediate window.
Debug.Print strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End If
End If
End With
Next
Set ctl = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description & vbNewLine _
& Err.Number, vbOKOnly, "Error"
 End Sub

[June7]

Why do you need to save the name? The ID is the value of the combobox. If the purpose of Audit table is to show before and after values of controls, then that's what it is doing.

If you must, then need to reference combobox column that holds the name. Column index begins with 0 so the name is index 1:

& cDQ & Me.comboboxname.Column(1) & cDQ & ")"

However, have no idea how to pull the 'old' value of Column(1).

Is this sub called from control BeforeUpdate event?

[accote]

Hi, thanks for the answer.
The reason I need the name and not the number is because I have a History Form that can be sorted
by any of the Audit Table fields. The boss wants to have a complete detailed history of every entry/edit made within
the database with dates of every field that has changed in any way. Having the number there would require to look-up
both the before and after value to know if a Contact was transferred to a different Company.
I guess they'll have to deal with it the way it is, just wish SharePoint would accept text data fields in a relationship, that
would make everything much easier.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Call AuditTrail(Me, ID)
End Sub

[ssanfu]

Since the values and names are in a table, you might use a function to get the Institution name.
Something like this (air code - untested!!):

<snip>
            If (.Value <> .OldValue) Or ((Not IsNull(.OldValue) And IsNull(.Value))) Or ((IsNull(.OldValue) And Not IsNull(.Value))) Then
               varBefore = GetInstName(.OldValue)
               varAfter = GetInstName(.Value)
               strControlName = .Name
<snip>




'-------------------------------------
Function GetInstName(pID As Variant) As String
'
'returns the Institution Name as a string
' when passed the Institution ID
'
   Dim d As DAO.Database
   Dim R As DAO.Recordset
   Dim sSQL As String

   'set default return value
   GetInstName = ""

   ' if pvalue not NULL or Empty
   If Len(Trim(pValue)) > 0 Then
      Set d = CurrentDb
      sSQL = "SELECT [Institution Name]"
      sSQL = sSQL & " FROM Institution"
      sSQL = sSQL & " WHERE [Institution ID] = " & pID

      Set R = d.OpenRecordset(sSQL)
      GetInstName = R("Institution Name")
      R.Close
      Set R = Nothing
      Set d = Nothing
   End If

End Function

(I really hate spaces in object names )

[accote]

Thank you Steve for the input, but where would I put the function? I'm not that good with the coding :-(
Also would that function interfere with the other values that end up in the same place, since the module pulls
the before & after value from a huge variety of different forms/objects?

Thanks again

accote

[June7]

You could call function in code, query, or textbox. Steve is showing example of calling the function from the sub procedure and setting the variables. If you only want to call the function when SourceField is Institution, include an If condition in your code. An alternative to the UDF with recordset is DLookup right in your sub.

varBefore = .OldValue
varAfter = .Value
strControlName = .Name
If strControlName = "Institution" Then
varBefore = DLookup("Company","Institutions","InstitutionID=" & .OldValue)
varAfter = DLookup("Company","Institutions","InstitutionID=" & .Value)
End If

Are there other comboboxes that present this same issue? Generic code could probably be structured to handle all.

The INSERT sql action works without apostrophe delimiters for text data? EDIT: Never mind, Steve pointed out below how that is handled. I saw that cDQ and forgot to go back and figure out what it was for. Personally, I prefer apostrophe instead of paired quotes and don't bother with a constant.

[ssanfu]

but where would I put the function?

You could put the GetInstName() code below the Audit Trail code.

Also would that function interfere with the other values that end up in the same place, since the module pulls
the before & after value from a huge variety of different forms/objects?

The forms are not a problem. There is only one control type being tested for and that is a combo box. Changes to text boxes do not appear to be saved.

<snip>
         If .ControlType = acComboBox Then
            If (.Value <> .OldValue) Or ((Not IsNull(.OldValue) And IsNull(.Value))) Or ((IsNull(.OldValue) And Not IsNull(.Value))) Then
               varBefore = .OldValue
               varAfter = .Value
               strControlName = .Name
               'Build INSERT INTO statement.
               strSQL = "INSERT INTO " _
                        & "Audit (EditDate, User, RecordID, SourceTable, " _
                        & " SourceField, BeforeValue, AfterValue) " _
                        & "VALUES (Now()," _
                        & cDQ & Environ("username") & cDQ & ", " _
                        & cDQ & RecordID.Value & cDQ & ", " _
                        & cDQ & frm.RecordSource & cDQ & ", " _
                        & cDQ & .Name & cDQ & ", " _
                        & cDQ & varBefore & cDQ & ", " _
                        & cDQ & varAfter & cDQ & ")"
               'View evaluated statement in Immediate window.
               Debug.Print strSQL
               DoCmd.SetWarnings False
               DoCmd.RunSQL strSQL
               DoCmd.SetWarnings True
            End If
         End If
<snip>

You have a test "If .ControlType = acComboBox Then"; if the control type is not a combobox, then all of the code above is skipped.
From the code, I would think you have more code that tests for text boxes. If you don't have more code, how are the changes to text boxes being saved? What am I missing???


You could add another test:

<snip>
         If .ControlType = acComboBox Then
            MsgBox "found combo box" & "   " & .Name
            If (.Value <> .OldValue) Or ((Not IsNull(.OldValue) And IsNull(.Value))) Or ((IsNull(.OldValue) And Not IsNull(.Value))) Then
               If .ControlType = acComboBox Then
                  varBefore = GetInstName(.OldValue)
                  varAfter = GetInstName(.Value)
               Else
                  varBefore = .OldValue
                  varAfter = .Value
               End If
               strControlName = .Name
               'Build INSERT INTO statement.
<snip>

[ssanfu]

@June,
that threw me also. Then I saw that there was a constant declared

Const cDQ As String = """"

[accote]

Thanks Guys, I will try the above first thing tomorrow :-)

Yes, I do have another module for the text boxes, as I couldn't figure out how to get the module
to work with both combo & text boxes. The other one is AuditTrail1 and no problems there, and
both work together nicely side by side.

Will let you know how I made out, thanks again and have a Good Night.

accote

[accote]

OK, I've spend 2 hours on this now and I can get one or the other, but not both.
If I use:

If .ControlType = acComboBox Then
If (.Value <> .OldValue) Or ((Not IsNull(.OldValue) And IsNull(.Value))) Or ((IsNull(.OldValue) And Not IsNull(.Value))) Then
varBefore = .OldValue
varAfter = .Value
strControlName = .Name
If strControlName = "Company" Then
varBefore = DLookup("Company", "Institutions", "InstitutionID=" & .OldValue)
varAfter = DLookup("Company", "Institutions", "InstitutionID=" & .Value)
'Build INSERT INTO statement.

I get the company name fine just as I'd like to have, but it ignores all my other combo boxes.
If I use Steve's Code with the "Else" I get everything but it shows the number again for the company.
I tried to switch them around in any way possible, but nothing gives me both?

accote

[June7]

You don't show End If line. In my example it would be just above: 'Build INSERT INTO statement

[accote]

June, the "End If" has to be on the bottom of the module otherwise I get errors with the "End With" and "Next"

Also, I tried the whole thing with making yet another module and Calling it "AuditTrail2" so running 3 modules, that
kind of solves the problem except I get the "Company" in there twice once as a name and once as a number.
Is there a way to add to the code to not look at the "Company" field/combobox in one of the modules?
Of course it would be nice to have only one module instead of 3, but it is what it is.

What do you think?

Thanks again for all your input

accote

[June7]

You need an End If for each If Then.

My suggested code is a complete If Then End If structure that just needed to be inserted into the original code. No other changes were required. All the existing End Ifs, End Withs, and Nexts would have remained as they were.

[accote]

June, you are right, must have been when I did the other code with "ELSE" in it. I just tried it again and
HURRAY it is working now. I can eliminate AuditTrail # 3 everything is just the way I waned it.
Thank you so much, you help was truly appreciated, you are great.

Working AuditTrail Code:

Option Compare Database

Const cDQ As String = """"

Sub AuditTrail(frm As Form, RecordID As Control)
'Track changes to data.
'recordid identifies the pk field's corresponding
'control in frm, in order to id record.
Dim ctl As Control
Dim varBefore As Variant
Dim varAfter As Variant
Dim strControlName As String
Dim strSQL As String
On Error GoTo ErrHandler
'Get changed values.
For Each ctl In frm.Controls
With ctl
'Avoid labels and other controls with Value property.
If .ControlType = acComboBox Then
If (.Value <> .OldValue) Or ((Not IsNull(.OldValue) And IsNull(.Value))) Or ((IsNull(.OldValue) And Not IsNull(.Value))) Then
varBefore = .OldValue
varAfter = .Value
strControlName = .Name
If strControlName = "Company" Then
varBefore = DLookup("Company", "Institutions", "InstitutionID=" & .OldValue)
varAfter = DLookup("Company", "Institutions", "InstitutionID=" & .Value)
End If
'Build INSERT INTO statement.
strSQL = "INSERT INTO " _
& "Audit (EditDate, User, RecordID, SourceTable, " _
& " SourceField, BeforeValue, AfterValue) " _
& "VALUES (Now()," _
& cDQ & Environ("username") & cDQ & ", " _
& cDQ & RecordID.Value & cDQ & ", " _
& cDQ & frm.RecordSource & cDQ & ", " _
& cDQ & .Name & cDQ & ", " _
& cDQ & varBefore & cDQ & ", " _
& cDQ & varAfter & cDQ & ")"
'View evaluated statement in Immediate window.
Debug.Print strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End If
End If
End With
Next
Set ctl = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description & vbNewLine _
& Err.Number, vbOKOnly, "Error"
 End Sub

[ssanfu]

Yes, I do have another module for the text boxes, as I couldn't figure out how to get the module
to work with both combo & text boxes. The other one is AuditTrail1 and no problems there, and
both work together nicely side by side.

If you decide you want to merge AuditTrail and AuditTrail1, post back. It is very easy to do. Since you have the code working like you want, I won't post my example (unless you want to see how).

In my view, it is better to have one subroutine to maintain rather than two.