[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBA Generic Way to address a field

Posted on 2011-03-01
10
Medium Priority
?
414 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 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

831 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