Solved

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

Posted on 2013-01-21
9
396 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 57
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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 57
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

737 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