VBA Generic Way to address a field


I am writing an audit trail for changes to tables etc (I know - reinventing the wheel.  But at least its my wheel).

Take a table with 50 fields.
I want to do a comparison in each beforeupdate event such as ..
... If field1.oldvalue <> field1.value then ...

I am trying to minimise the code for obvious reasons.

Rather than repeat this above "IF" code I was wondering if it was possible to somehow just call a function that simple refers to the "current field".  

 In order words, I do not want to specify field names - I want the system to know that "current field" means the field that the "event" started from.

So if I was in field 25 and used a "BeforeUpdate" event - the system would know that "current field" meant field 25.

Is there such a facility??

Is this clear??  

Patrick O'DeaAsked:
Who is Participating?
reb73Connect With a Mentor Commented:
You could just use 'Me' to reference the current object

like Me.Name or Me.Index
Patrick O'DeaAuthor Commented:
Thanks reb73,

That's close ..

Me.Name gives me the formname.

How do I get the name of the individual field?
Me will give you the field name if the event handler pertains to a field. Me refers to the active object, which will be the form if no control in the form is selected

If you do a debug.print me.name within the beforeupdate or event handler, you should see the field name
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Patrick O'DeaAuthor Commented:

See simple frm1 attach.
Amend the "name1" field on frm1.

The formname will then be displayed (not the field name).

AM I missing something??

GugroConnect With a Mentor Commented:
I am sorry but there no facility  in Access. The closest that you can have is to write a common sub which gets called from each BeforeUpdate with Me.ActiveControl as parameter.
But you still have to create all the BeforeUpdate sub's by hand ( or you may write a code generator which creates the code for you )

roger_karamConnect With a Mentor Commented:
You can try looping through all the text boxes, but that might be annoying at times...


Dim cCont As Control

    For Each cCont In Me.Controls

        If TypeName(cCont) = "TextBox" Then

            If cCont.OldValue <> cCont.Value Then
            MsgBox (cCont.Name & " changed from " & cCont.OldValue & " to " & cCont.Value)
            End If
        End If

     Next cCont

'From http://www.ozgrid.com/VBA/control-loop.htm
forgot to mention, you would want to do this on the form_beforeudpate event, and not the field_beforeupdate.

here is your example with this implemented. Please note that the form_update event takes place when you save the record (including going to a different record)
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
see this links

How to create an audit trail of record changes in a form in Access

Creating an Audit Log
Patrick O'DeaAuthor Commented:
Thanks you all!
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.