Ms Access getting a function name of a function being executed

Question

Is there any way of being able to determine, either programatically or through a system value (or any other way) the name of a function or sub while I am actually executing code in that function or sub?

I'm running Access 2007 with XP.

Stuart Aitchison.

Answer 1

VBA is single threaded. If you want to know the name you would have to assign it to a variable, but I don't know what good that would do you.

Can you explain why you need this information?

---

Bill Mosca
http://www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 2

Bill, thanks.

I rather thought that this was the case.

I want to use it in error handling, and for logging on to an error file.

Regards, Stuart Aitchison.

Answer 3

Stuart

Best thing I've found for error traps is mztools.com It's free ware and comes with templates that have bracketed places that will autofill. My template looks like this:

    If gbolErrorTrapOff = False Then On Error GoTo err_PROC
	{PROCEDURE_BODY}
exit_PROC:
	On Error Resume Next
	Exit {PROCEDURE_TYPE}
err_PROC:
	Dim strErrMsg as String
	Dim lngIcon as Long
	
    Select Case Err.Number
		Case 2501
			'Open cancelled.
			strErrMsg = "No information for selected filter."
			lngicon = vbOKOnly+vbinformation
        Case Else
            gProcName = "{PROCEDURE_NAME}"
            glngErrNum = Err.Number
            gstrErrDescr = Err.Description
			glngLineNum = Erl
            Call ErrorLog("{MODULE_NAME}")
    End Select
    If strErrMsg <> "" Then MsgBox strErrMsg, lngicon, _
		GetSummaryInfo("Title", False)
    
    Resume exit_PROC

Things like the module name, procedure name will fill when you click on the error trap icon. The code will then look like this:

Function TestCode(strIn As String, strFolder As String) As String
    If gbolErrorTrapOff = False Then On Error GoTo err_PROC
    Debug.Print CurrentProject.Name
exit_PROC:
    On Error Resume Next
    Exit Function
err_PROC:
    Dim strErrMsg As String
    Dim lngIcon As Long
    
    Select Case Err.Number
        Case 2501
            'Open cancelled.
            strErrMsg = "No information for selected filter."
            lngIcon = vbOKOnly + vbInformation
        Case Else
            gProcName = "TestCode"
            glngErrNum = Err.Number
            gstrErrDescr = Err.Description
            glngLineNum = Erl
            Call ErrorLog("Module1")
    End Select
    If strErrMsg <> "" Then MsgBox strErrMsg, lngIcon, _
        GetSummaryInfo("Title", False)
    
    Resume exit_PROC
End Function

I use that template for every procedure. A Sub named ErrorLog is called. That Sub inserts a record into my ErrorLog table capturing all the stuff I need for debugging.

You can also add line numbers with mztools so Erl will have the number where the error occurred.

Cool, huh?

---

Bill Mosca
http://www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 4

Bill,

Thanks very much for the info.

I'll have to do some processing and see how I can work all this.

If no-one else has any ideas then I think that this post shoudl be closed.

There are certain things I now have on my Access wish list:

1.  Being able to get a function or sub name from within the current function or sub using a system variable or some other such immediate and direct mechanism.

2.  Having more control over text boxes, command buttons and one or two other objects, by way of shape, size, colour manipulation and so on on an ad hoc basis.

3.  Definitely more direct control over the shape and colour and being able to directly change the colour, text, text attributes and so on of specific tabs rather than going through the tab control, also on an on-the-fly basis.

Regards, Stuart Aitchison.

Answer 5

You're welcome, Stuart.

Keep in mind some things cannot be changed on a form while it is open. Colors probably; shapes probably not.

Also remember that form design is an art. I've seen some pretty hideous stuff out there. The simpler it is the better the user will like it when it comes to actually working with it.

And I honestly don't think the procedure name is available while it is running. mztools reads the name and populates the {PROCEDURE_NAME} when it writes the error handler. Play around with mztools and I think you will find it an indispensable set of tools. I list a few of my favorite tools on my website, ThatllDoIT.com on the home page.

---

Bill Mosca
http://www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 6

While it is possible, since the "Call Stack" window does it ( Ctrl_L in VBE ), it is not exposed in a manner that I am aware of. You can try to open the said window, and, programatically, try to read what appears in it, to get the "actual" sub name, but that is more a hack than an elegant solution.

Answer 7

Bill,

Thanks very much for the info.

I'll have to do some processing and see how I can work all this.

If no-one else has any ideas then I think that this post shoudl be closed.

There are certain things I now have on my Access wish list:

1.  Being able to get a function or sub name from within the current function or sub using a system variable or some other such immediate and direct mechanism.

2.  Having more control over text boxes, command buttons and one or two other objects, by way of shape, size, colour manipulation and so on on an ad hoc basis.

3.  Definitely more direct control over the shape and colour and being able to directly change the colour, text, text attributes and so on of specific tabs rather than going through the tab control, also on an on-the-fly basis.

Regards, Stuart Aitchison.

Hi Stuart,

Quite a number of wishes!

With respect to nr.1, you could assing the name of the proc to a global variable in the beginning of the sub of function. It is comparable to a systematics that I use to activate a Stop at a certain location for debugging purposes without going to the code first.

With respect to nr. 2, you can change almost anything of the form when it is opened: colours, sizes, positions, visibilities, etc. Even RecordSources or RowSources. This is my basis for using only one generalized form for all continuous forms. During the opening all controls are defined in such a way that the form represents the specific character of the data. All changes are - happely - dynamic, and nothing is stored at closing.

With respect to nr.3, I do not use tabs. Instead I use CommandBoxes or a menu, both different representations of the same data. At will I can use the one or the other.

As Bill already stated, form design is an art, and my approach demands for quite a dose additional structure. It also asks for quite a lot of programming.

But once you are through, the next application is already configured except for the data.

Imb.

Answer 8

Some very interesting input all round.  Thanks to everyone for the time and trouble.

I am actually using a menu set up.  I've set some sub forms on a main form, with the first sub form being the "main" menu, which is populated with a number of "buttons" (in my case I used text boxes, although command buttons would serve just as well - hence my comments about shapes).  When the user selects one of the "main" buttons, it causes another sub form to appear, which is simply a list of available items linked to the code associated with the button selected.  The main and sub menu options are held on individual files, and I've linked the menu options to groups through a separate file, so this enables me to set up a button for almost anything that goes.  Each user can have their own individual button, for example.  The concept can be extended to suit the need of the situation.

The coding on the main screen/form is minimal.  The coding on the "main" sub form is relatively extensive, and the coding on the other sub forms is again fairly minimal.  Originally I had one list sub form, but have now extended this to ten, and hence the issues now arising with tabs and so on.

The colour of each button is preset on the main options file, and the only thing I have not yet done is to try and give the tabs the colours associated with the buttons selected.  Given what I've been told so far, I'm not going to try this, but I have succeeded in getting the records on each sub form to have the colour associated with the button selected, so I'm going to leave it at that.  The 80/20 rule and so on.

As far as putting the the name of the current sub or function into a public variable is concerned, it seems that this would be a good way to go.  I've just discovered the joys of public variables and constants in class modules, and have gone a bit moggy with them, but I have a horror of hard coding values.  After 37 years, a large slice of it spent grovelling around in other people's code, I'll do almost anything to avoid hard coding values, so, either public variables or values on a genereic file.

I've also got to comment that the most salutory experience I've had in my 37 years is having to go back into my own code and work out what the "freak" I was doing!!!!!!!!!!!!!!

Thanks again for the input.

Regards, Stuart Aitchison.

Answer 9

Correct it is not exposed in the object model. Wayne Phillips found a way around that: http://www.everythingaccess.com/vbwatchdog.htm

---

-Tom. Microsoft Access MVP

Answer 10

As far as putting the the name of the current sub or function into a public variable is concerned, it seems that this would be a good way to go.  I've just discovered the joys of public variables and constants in class modules, and have gone a bit moggy with them, but I have a horror of hard coding values.  After 37 years, a large slice of it spent grovelling around in other people's code, I'll do almost anything to avoid hard coding values, so, either public variables or values on a genereic file.

Hi Stuart,

It is very important to keep a horror of hard coding values.

However, you can distinguish between user data and meta data, the latter being data that is used to influence or steer the software. This kind of data is part of the software. In the same way as you can use certain error codes in hard coded if-then-statements.
So, I would not hesitate to do that. The alternative would be placing all this in a table, but that only works the wrong way around in my opinion. Moreover, the assignment of a global to hold the sub of function name is not more hardcoded than the declaration of the procedure itself. But, if you rename the sub name, you also have to supply the new new name to the global. I think that is in the same line as you have to rename all calls to the sub anyway.

In your reply you say that you use external files. In my generalised approach I use tables to store all data on how forms and all the controls should behave. This is also meta data and therefore these tables are part of the FE of the application, and not the BE.

My every day experience is that - though I have been gone through many development cycles - there is still so much to improve, an almost unendless task: it keeps me vivid.

Imb.

Answer 11

Imb-hb:

When I talked of putting the procedure name in a global variable that would have to be done from within the actual procedure itself.  Setting up a long list of procedures would not really serve any purpose, but a global variable with the name of the current procedure would achieve to some extent what I am after.

My apologies if I have been a bit misleading.  I have used terminology rather loosely in referring to files.  I actually mean tables within the database itself, just to clear the point.  Having lectured this stuff I should be more specific.  They were files when I started out, but thanks to Boyce and Codd and the like became known as tables with rows and tuples and so on.  File-table; record-row; field-tuple-column.

I generally store as much of the meta data in tables as is both possible and reasonable, but have now resorted to global public constants for some of it, which, for my purposes, has the same effect, and keeps the data in one place where it can be easily accessed, which is really the object of the exercise, as long as it is done consistently.

Thanks again for your input.

Regards,

Stuart Aitchison.

Answer 12

Tom, thanks.

I've had a look.  This seems to be a really serious  piece of software.  Immediately the cost, in South African Rands, is bit rich for my immediate resources (I am self employed), but, given the benefits, is well worth saving for.

Regards,

Stuart Aitchison.

Answer 13

Correct it is not exposed in the object model. Wayne Phillips found a way around that: http://www.everythingaccess.com/vbwatchdog.htm

---

-Tom. Microsoft Access MVP

One has to ask why Microsoft doesn't buy them (i.e. SysInternals) and integrate the error handling into VBA?

---

http://www.saberman.com

Answer 14

 Microsoft already knows how to do it (hey, after all, they did the call stack! which does display the calling procedure), but I doubt that Microsoft has plans to touch VBA and VBE. Anyhow, Microsoft has no need to buy them to do the same, if they want to do it.

 Note that the beast who handles the call stack is probably closer (than VBA) to the OS itself, since other languages also expose that "call stack" like information. So, I assume it is an OS thing. Also, that call stack has to handle "callback" stuff, and stuff not necessary known at compile time, so, again, something pointing to the OS rather than VBA. Maybe you can try with WMI to get that information from WIN32 processes and tasks, but I fail to see anything immediatly usable there too.

Answer 15

No, the handling and the formatting of a call stack has to done a component of the language.

The VBA call stack display from Microsoft only includes VBA code.  Notice that anytime the code crosses a boundary out of VBA and back in all the stack trace says is none VBA code.  The interactive portion of it -- being able to double click and go to the line of code at that level is also very language specific.

Your are correct that Microsoft probably won't do anything to provide access to the call stack as it is in the process of killing off VBA the way it did the standalone VBE.

---

http://www.saberman.com

Answer 16

Sorry, but that handling does NOT have to be handled within the component of the language. As example, see the following reference, which seems to be about Win32 and C, http://support.microsoft.com/kb/92537

Basically, if the compiled state still contain the debug-symbols-information, an external tool  (not on the same task than the one being "debugged") CAN know from which subroutine and, if optimization didn't screw up the lines, the line calling it. That external tool, ot necessary written in VBA to debug VBA code, can then also move the execution to the next line (of the foreign task that you are tracing). Sure, the debug information must be present in the compiled file.

Answer 17

Stuart,

Thanks for this discussion.  I come to it by way of trying to do the same thing you are,  trap the procedure AND the module for error handling purposes.   I agree, assigning a value to a variable in each procedure is not a time saver.

---

TIA, Paul