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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

Referring to Variant fields within a FUNCTION

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
Patrick O'Dea
Asked:
Patrick O'Dea
  • 4
  • 3
  • 3
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
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
 
Patrick O'DeaAuthor Commented:
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
 
IrogSintaCommented:
It's not really clear in your database what you're trying to do.  Can you explain what you want to accomplish?
0
Industry Leaders: 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!

 
Patrick O'DeaAuthor Commented:
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
 
IrogSintaCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Patrick O'DeaAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
did you test the db i uploaded?
0
 
IrogSintaCommented:
You can compare the original and new fields without the use of an array.  Check out this revised database.
EESunday.accdb
0
 
Patrick O'DeaAuthor Commented:
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now