Error Trapping - Getting the actual syntax that causes the error 2

[mdav2]

I want to get the exact syntax that cause an error. I am using the code below. I can trap the easy errors such as

* Error Description
* Error Number
* Date Error Occurred
* The control that caused the error

The most useful information would be the line of code that causes the error.

In my example below this is "test = Me!Text + Me!Number", adding text to a number. If this could be transferred into a variable then it would be really useful.

Each command must be stored in some property of the form, or control. I've tried looking in books, etc and also using the debug but have not come up with anything.

I even though about using the execute command to pass each line of code as a string but that would be impractical. The other way is get the code to break on the error, but this isn't very professional.

If anyone can help me here I would be most appreciative. Thanks.

Can anyone help me

--------
- Code -
--------
Private Sub Command0_Click()
' Error handler
On Error GoTo errHand

Dim rstError As Recordset
Dim dbs As Database
Dim test
Dim sqlstr
Dim strDate As String
Dim control
Dim strControlName

' line to cause error
test = Me!Text + Me!Number

' Error handler
errHand:

Set control = Screen.ActiveControl
strControlName = control.Name
MsgBox strControlName

MsgBox "Any Error Has Occurred In The Application - Please Contact Your Support Staff", , "System Error"
Set dbs = CurrentDb
strDate = CDate(Date)
sqlstr = "Insert into T_Errors (ErrorMessage, ErrorNumber, FormName, ErrorDate) values ('" + Err.Description + "','" + str(Err.Number) + "','" + Me.FormName + "','" + strDate + "')"
dbs.Execute (sqlstr)

End Sub

 

[jimmythegeek]

If I understand your question right, then have you tried:

test = Me!Text & Me!Number

You are trying to "add" a text value to a number value by using the +. In SQL Server, the + is the concatenation key, but in Access, the "&" is the concatenation key.

Also, the way you have the error handling set up, is completely wrong, as the code will continue to run into the errhand label every time whether there is an error or not.

Something like the following would be correct:

==========
Private Sub Command0_Click()
On Error GoTo HandleError
Dim rstError As Recordset
Dim dbs As Database
Dim test
Dim sqlstr
Dim strDate As String
Dim control
Dim strControlName

' line to cause error
test = Me!Text + Me!Number

ExitError:
Exit Sub

HandleError:
msgbox Err.number & ": " & Err.Description
Resume ExitError
End Sub
==========

If you don't have an Exit sub before the HandleError label, the code will run straight through it all without exiting till it hits the End Sub.

If this doesn't help, or is not what you are talking about, then let us know.
Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[mdav2]

I didn't make this very clear, sorry.
The line

test = Me!Text & Me!Number

causes the error but I want to try and log this line as the error. I have found the erl() function can do this but requires you to number each line as a label, eg

1:
test = me!text + me!number

If there is a way to get access to return the syntax causing the error (the test= me!text + me!number) line then that would be better as I don't fancy numbering every line of VBA.

 

[RickSpr]

You can access the source code for a module, provided you have permission, by using the DAO Module object. (The permission required is the Access user-level security Read Design permission for the form, report, or module.) However, there's no built-in way to figure out which source line corresponds to the error statement.

But all is not lost. When I need to know what statement an error is occurring on, I add numeric line labels to all the executable statements. When an error occurs, the no-longer-documented ERL() function returns the numeric line label, if any, on the statement where the error occurred. If the statement doesn't have a numeric line label, ERL() returns the line label of the most recently executed statement that had one. By logging ERL(), I can relate the error back to the source. The only problem is that ERL() will go away in a future version, I expect, at which time your error handling code will break.

If you really want the source code, and not just the line label, you could use ERL() to find it. You'd have to number all the executable statements in your module, of course, and you'd have to hard-code the module name into each module as a constant named, say, ModuleName. Then you could pass the module name to an error logging routine that collects ERL() and Err object information, uses the module name to get the Module object, and searches for the line number.

There's just one catch: The Modules collection only contains a Module object for each module that's currently open in Design View, so you'll have to use OpenModule first. That means, of course, that you'll have to distribute your source code, and the currently logged on user will have to have permission to open the module. That exposes you to having your code changed or stolen. But I have a workaround: If you use Access user-level security, you can include a User account that has Read Design permission in the workgroup file you distribute. You can then have the error logging procedure disable screen painting (Application.Echo = False), create a temporary Workspace object, logging on to it with that user account, and open the module in Design View. When you're done getting the source code, close the module and destroy the workspace, then turn Echo back on.

Keep in mind that if this error logging procedure returns to the procedure that had the error, Err will be cleared. The logging procedure should either terminate the application, or should pass the Err properties back in module-level variables so the error handler that called it can rebuild the Err object.

Jeez, that's a lot of work, isn't it? Only you can say whether it's worth it to you to have the source statement. Rick Sprague

 

[scking]

Actually, the function erl has not been documented for quite a while but I read recently it is still included. BUT, to get erl working you need to number the lines of code manually as follows:

Public Sub TestErrLine()

1 Dim strName As String
2 Dim strAddr As String
3 Dim intCtr As Integer

On Error GoTo HandleErr

4 For intCtr = 1 To 20
5 If intCtr = 10 Then
6 Err.Raise 9999, "MySource", "It's Bad"
7 End If
8 Next intCtr

Exit_Proc:
Exit Sub

HandleErr:
MsgBox Erl
Resume Exit_Proc
Resume
End Sub

The msgbox will return 6. I just build and tested this. Just add the other return information you need using the Err.

Steve King

 

[MoGryph]

I think this might help you out alot as well...

I've been coding VBA in Access for about 6 years now, and just last night found this Property of the Application object... (Don't know if it's new to Access 97 or not, but it's in it. I currently don't like Access 2000... yet).

Did you know about the CodeContextObject property? Direct quote from Access' Help:

"You can use the CodeContextObject property to determine the object in which a macro or Visual Basic code is executing."

Look it up in Help, and check out the Example. It's really a GREAT function if you ask me. Beats the heck out of trying to use Me.Name to return what object's code is erroring- Me.Name

Here's another direct quote, which completely express why I think it's so great...

"The

ActiveForm

 and

ActiveReport

 properties of the

Screen

 object always return the object that currently has the focus. The object with the focus may or may not be the object where a macro or Visual Basic code is currently running, for example, when Visual Basic code runs in the Timer event on a hidden form."

They even give you an example Error handler function.

I know I'll be going back and revising some of my old code!

Hope it helps

-MoGryph
[8O)