?
Solved

VBA Generic Way to address a field

Posted on 2011-03-01
10
Medium Priority
?
408 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 500 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

764 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