Solved

Referring to Variant fields within a FUNCTION

Posted on 2013-01-20
10
381 Views
Last Modified: 2013-01-20
See attached - very very simple database.  

See FORM1
Note the variant called "AllFields"

I want a new function to be called from Private Sub Form_BeforeUpdate.

This function will display the actual value in each field in the array "AllFields".
So, for instance, the function will display the name "Tom", "123", etc.

The function will display the "OldValue" and the current value of the field.

Could someone give me guidance as to how I can displays these values.
Actually, what I really need is the few lines of VBA to show these values.
Also, I am uncertain of any DIM statements in the function.
EESunday.accdb
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
  • 4
  • 3
  • 3
10 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38798896
see this link
http://support.microsoft.com/?kbid=197592

the Function AuditTrail() will show you how to display the Old and New values of controls
0
 

Author Comment

by:Patrick O'Dea
ID: 38798907
Thanks capricorn1,

I am familiar with this audit trail and have used it in the passed.
However, I am developing my own one now.

Consequently, I am passing an array of fields in a variant into a FUNCTION.

However, I am unsure how to access these fields withing the function.

... as described in my original post above.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38799016
It's not really clear in your database what you're trying to do.  Can you explain what you want to accomplish?
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:Patrick O'Dea
ID: 38799057
Ok,
I will try to clarify.

1. I have a form for maintaining peoples names.   (There will be other forms too).

2. Before I save the form, I call a function that compares the OldValue and the current Value of a number of fields.

3. So in my peoples form (VBA), I have a variant AllFields= ("MyName", "MyAge","MyAddress")

4. In the function, I want to split this "Allfields" into its individual components.  So for instance I want to see the value held in MyName.OldValue   etc.  However, I do not know the syntax for this.    WHAT IS THE SYNTAX?

The complication for me arises because I am doing this in a function.  If I hardcoded it in the original code it would be easier.

Hopefully this is clearer??

Thanks for help.
0
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 100 total points
ID: 38799092
Not really. What is the purpose of the function?  What will it do when it finds something different in the comparison?  When will this function run?  When a field is changed?  When a button is clicked?  I'm just trying to figure out what your ultimate goal is here.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 400 total points
ID: 38799097
try this codes

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim AllFields()
AllFields = Array(txtMyName, txtMyAge)
' CALL FUNCTION FROM HERE ********************

test1 (AllFields)
End Sub



Function test1(strFields)
Dim j
For j = LBound(strFields) To UBound(strFields)
    Debug.Print strFields(j)
Next
End Function
EESunday.accdb
0
 

Author Comment

by:Patrick O'Dea
ID: 38799164
Thanks capricorn1,

We are probably nearly there...

However, your suggested line ;

 Debug.Print strFields(j)

This line shows the CONTROLNAME   (E.g. MyName)
However, I what the actual value stored in this CONTROLNAME  (E.g. Tom)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38799172
did you test the db i uploaded?
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38799263
You can compare the original and new fields without the use of an array.  Check out this revised database.
EESunday.accdb
0
 

Author Closing Comment

by:Patrick O'Dea
ID: 38799279
Thanks capricorn1.<br />I missed your database attached in the last posting.<br />I merely read the code and it did not appear to do what I wanted.<br /><br />However, I stand corrected and all works perfectly now.<br /><br />Thanks again,<br /><br />(Thanks to IrogSinta too)
0

Featured Post

Independent Software Vendors: 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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 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