Solved

VBA Generic Way to address a field

Posted on 2011-03-01
10
398 Views
Last Modified: 2012-05-11
All,

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??  









0
Comment
Question by:Patrick O'Dea
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 25

Accepted Solution

by:
reb73 earned 125 total points
ID: 35006704
You could just use 'Me' to reference the current object

like Me.Name or Me.Index
0
 

Author Comment

by:Patrick O'Dea
ID: 35006783
Thanks reb73,

That's close ..

Me.Name gives me the formname.

How do I get the name of the individual field?
0
 
LVL 25

Expert Comment

by:reb73
ID: 35006819
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Patrick O'Dea
ID: 35006897
reb73,

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

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

AM I missing something??

Database4.zip
0
 
LVL 6

Assisted Solution

by:Gugro
Gugro earned 125 total points
ID: 35006899
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 )

0
 
LVL 5

Assisted Solution

by:roger_karam
roger_karam earned 125 total points
ID: 35006988
You can try looping through all the text boxes, but that might be annoying at times...

-RK

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
0
 
LVL 5

Expert Comment

by:roger_karam
ID: 35007070
forgot to mention, you would want to do this on the form_beforeudpate event, and not the field_beforeupdate.

-RK
0
 
LVL 5

Expert Comment

by:roger_karam
ID: 35007109
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)
Database4-v2.zip
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 125 total points
ID: 35007150
see this links


How to create an audit trail of record changes in a form in Access
http://support.microsoft.com/?kbid=197592


Creating an Audit Log
http://allenbrowne.com/AppAudit.html
0
 

Author Closing Comment

by:Patrick O'Dea
ID: 35007371
Thanks you all!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question