?
Solved

Referring to Variant fields within a FUNCTION

Posted on 2013-01-20
10
Medium Priority
?
382 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 400 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 1600 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

765 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