asked on

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

als315

Look at sample here:
http://forums.devarticles.com/microsoft-access-development-49/creating-audit-trail-of-all-edits-to-database-22382.html

Patrick O'Dea

ASKER

Thanks als315,

I have used other peoples audit trails before.

However I would prefer to complete my own.

Thanks anyway for the suggestion.

Jim Dettman (EE MVE)

<<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.

Patrick O'Dea

ASKER

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

als315

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

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patrick O'Dea

ASKER

Thanks als315,

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

Patrick O'Dea

ASKER

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

Jim Dettman (EE MVE)

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

Sure, it's a great way to learn.

Jim.