[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Debug.print the variable name (as well as the actual value)

Posted on 2013-01-21
9
Medium Priority
?
408 Views
Last Modified: 2013-01-21
This is simple but a little tricky to explain.

Example:

1. Run FORM1

2. Change the MyName field from "Pete" to "Joe"   (or similar).
3. Use the record selector to go to the next record.

4. System will STOP
5. A FUNCTION had been called which will write before/after values of each control passed.

6. Note that the before and after values are displayed in the immediate window.
7. So far , so good.

Question: How do I also Debug.print the variable name.  I.e. I also want to see the words "MyName" in the immediate window.  (And all the other field names as they loop)

This is a slightly odd question.
In other words I know the current (and old) value of a field... being "Joe" and "Pete" (or whatever).

However, I do NOT know the field name.
Database2.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
  • 2
9 Comments
 
LVL 40

Expert Comment

by:als315
ID: 38802147
0
 

Author Comment

by:Patrick O'Dea
ID: 38802170
Thanks als315,

I have used other peoples audit trails before.

However I would prefer to complete my own.

Thanks anyway for the suggestion.
0
 
LVL 58
ID: 38802202
<<Question: How do I also Debug.print the variable name.  I.e. I also want to see the words "MyName" in the immediate window.  (And all the other field names as they loop)>>

 On a bound form, your really dealing with controls, so what you want is:

 ? Me.<ControlName>.ControlSource

 I believe that's what your after, but if I missed the point of the question, let me know.

Jim.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Patrick O'Dea
ID: 38802242
Thanks Jim,

Let me clarify.

I am writing to my audit table details such as the following;

Control: MyName   Changed From : Joe   Changed To: Pete    Date: Jan 21 2013


In my existing code I am able to write all these details EXCEPT the one in bold.

So, rather bizarrely, I know the values held but not the name of the control.

(The database I attached is the easiest way to follow my query).

Hopefully I am making myself clear,

Padraig
0
 
LVL 40

Expert Comment

by:als315
ID: 38802275
Try this code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctrl As Control
On Error Resume Next
For Each ctrl In Forms("Form1").Controls
    If ctrl.OldValue <> ctrl.Value Then _
        Debug.Print ctrl.Name, ctrl.OldValue, ctrl.Value
Next ctrl
end sub

Open in new window

0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 38802315
Not sure what Anthony pointed you too, so this may be a duplication of effort, but take a look at this:

http://allenbrowne.com/AppAudit.html

I realize you want to do your own, but I would suggest using this for a couple of reasons:

a. It shows you how to do it<g>

b. It's already written and tested.

c. In looking at your code, it covers things you haven't thought of yet, such as adding and deleting records and not just changes in fields.


 I can't answer your question directly because with this:

Function RecordAudit(TableName, UniqueID, strFields)

 I'm not sure what strFields is exactly.  Normally, just as with a control, you could get a fields name like this:

  For j = LBound(strFields) To UBound(strFields)

Debug.Print strFields(0).Name, strFields(0), strFields(0).OldValue


 But I'm not quite sure what you have in strFields.

Jim.
0
 

Author Comment

by:Patrick O'Dea
ID: 38802316
Thanks als315,

However, I want to pass all the values to a FUNCTION so I can re-use the FUNCTION.
0
 

Author Closing Comment

by:Patrick O'Dea
ID: 38802377
Jim,

 strFields(0).Name

That's it !!

You have inadvertently given me exactly what I want.

 strFields(0).Name  ---> This gives me the name of the control ...which is what I wanted.

And can I be stubborn and write my own audit trail!

Thanks again,
Padraig
0
 
LVL 58
ID: 38802645
<<And can I be stubborn and write my own audit trail!>>

 Sure, it's a great way to learn.

Jim.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

656 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