Solved

VBA Generic Way to address a field

Posted on 2011-03-01
10
404 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Industry Leaders: 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!

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

717 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