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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

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
Dale FyeOwner, Developing Solutions LLCCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 Architect / Systems AnalystCommented:
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 FyeOwner, Developing Solutions LLCCommented:
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 Architect / Systems AnalystCommented:
OK ... Guess I missed that.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.