[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2013-01-21
9
Medium Priority
?
413 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 59
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

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 59

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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Implementing simple internal controls in the Microsoft Access application.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

591 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