Solved

VBA Generic Way to address a field

Posted on 2011-03-01
10
384 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
  • 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
 

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now