Solved

Referring to Variant fields within a FUNCTION

Posted on 2013-01-20
10
373 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
  • 4
  • 3
  • 3
10 Comments
 
LVL 119

Expert Comment

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

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 119

Accepted Solution

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

Expert Comment

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

746 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

8 Experts available now in Live!

Get 1:1 Help Now