Solved

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

Posted on 2013-01-21
9
392 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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Suggested Solutions

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

680 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