Solved

Referring to Variant fields within a FUNCTION

Posted on 2013-01-20
10
379 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 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

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…
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.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

821 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