Improve company productivity with a Business Account.Sign Up

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

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

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
Patrick O'Dea
Asked:
Patrick O'Dea
  • 4
  • 3
  • 2
1 Solution
 
Patrick O'DeaAuthor Commented:
Thanks als315,

I have used other peoples audit trails before.

However I would prefer to complete my own.

Thanks anyway for the suggestion.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Patrick O'DeaAuthor Commented:
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
 
als315Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
Patrick O'DeaAuthor Commented:
Thanks als315,

However, I want to pass all the values to a FUNCTION so I can re-use the FUNCTION.
0
 
Patrick O'DeaAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<And can I be stubborn and write my own audit trail!>>

 Sure, it's a great way to learn.

Jim.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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