Problem Implementing Access Audit Log - field.oldvalue "method not supported"

I've been trying to implement a simple Access audit log, using the excellent article by Dale Fry on this website. I can follow the logic and it does exactly what I'm looking to put in place but it falls over on a pretty fundamental point with my system/db.

The main sub checks the oldvalue property of each field to determine if the value has changed but I get a run-time error 437 (Object doesn't support this property or method). I have a straight-forward form linked to an MS-SQL table and the relevant code snippet is shown below - fails on line 3:

For Each fld In Form.RecordsetClone.Fields
...
    If (Nz(Form(fld.Name).OldValue, "") <> Nz(Form(fld.Name).value, "")) Then
        'field value has changed
        bRecord = True
    Else
        bRecord = False
    End If
...
Next

Open in new window


Any ideas would be most welcome, I'm sure I'm missing something fundamental but currently at the brick wall stage! Is the oldvalue method just not possible with ms-sql table?
nigelr99Asked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
nigelr99,

In order for that code to work properly, the controls on the form that is being audited must have the same name as the underlying field.

So, instead of txt_LastName bound to the [LastName] field, you must name the control "LastName".

Since I normally use a naming convention for my controls, I probably need to go back and relook that code to account for that possibility.

Dale
0
 
ste5anSenior DeveloperCommented:
Audit trails on SQL Server are better handled on the server-side. Either by CDC or triggers.

btw, even in Access I would favor triggers (aka data macros).
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You're showing a "Form" object - I assume that is set to the name of the form you're working with?

Are the controls in a Subform? If so, you'd refer to those differently:

Form.YourSubformCONTROLName.Form(fld.Name)

However, if you already have a Field object - i.e. your "fld" item - then you don't need to refer to the Form at all:

If Nz(fld.OldValue, "") <> Nz(fld.Value, "") Then
  <do something here>
End If
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
nigelr99Author Commented:
The form object is part of the sub declaration (straight from original article) as below..

Public Sub AuditLog(Form As Form, TableName As String, Action As String, PKFieldName As String)

Open in new window


(no sub-forms involved)
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Where is the "fld" object declared?
0
 
nigelr99Author Commented:
It's a local variable as below.
Dim fld As DAO.Field

Open in new window

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I see ... that's a DOA field. I was curious whether this was a Form Control (some refer to it as a Field).
0
 
nigelr99Author Commented:
Ahh! I see - many thanks. That makes sense instantly of course and something I should have picked up on at the outset :-/
0
 
nigelr99Author Commented:
Works as expected with control names modified. Thanks
May have follow-up question or 2!
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I'm a little confused. The OldValue property is a Property of a Control (not a Field), and has nothing to do per se with the corresponding Field in a Table
So "the oldvalue method just not possible with ms-sql table" ... would not be the problem.
I've never had to name the Control the same name as the underlying field name in order to access the OldValue property.

From Access Help:
"The OldValue property contains the unedited data from a bound control and is read-only in all views.
Microsoft Access uses the OldValue property to store the original value of a bound control. When you edit a bound control on a form, your changes aren't saved until you move to another record. The OldValue property contains the unedited version of the underlying data.
You can provide your own undo capability by assigning the OldValue property setting to a control. The following example shows how you can undo any changes to text box controls on a form:"
0
 
Dale FyeCommented:
Joe,

This has to do with the way I implemented the AuditLog code in my application.  If you read the article and look at my code, you will understand.  

I used the fields collection to loop through all of the fields in the forms recordsource, rather than the controls collection.  Don't remember off the top of my head why I did it that way, but I did.  That is why the code is not working properly when the control has a different name than the bound field.  I will probably relook this when I get a chance later this week.

Dale
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
OK ... Guess I missed that.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.