global form error handler (1 Viewer)

CJ_London · Sep 21, 2016

I have many forms with the same code in the form error handler event, and often this is the only code in the form. I would like to remove it from the forms and set hasmodule to false and in the event properties, rather than having [EventProcedure] have =frm_errHandler(....) with frm_errHandler being a public function in a module.

I use this quite a lot for other functions, but this is the first time I am stumped on the parameters to pass.

I know I can call the module function from within the usual form error handler event and pick the parameters up from there, but this means I cannot set hasmodules to false. The problem is how to pass the parameters in the call (e.g. =frm_errHandler(dataerr) ) - they are clearly populated on the call, but How do I pick them up from the module or the form environment?

as an example, this cut down version enables a user to enter a calculation into a numeric field. e.g. (2*3)+4 - will evaluate to 10.

Code:

 Private Sub Form_Error(DataErr As Integer, Response As Integer)
  
     Select Case DataErr
        Case 0 ' there is no error - note this case option not really required, just shown for completeness
             response=0
             SendKeys "{ENTER}"

         Case 2113 'there is text in numeric field
            'see if it will evaluate to a number
            On Error Resume Next
            Screen.ActiveControl.Text = Eval(Nz(Screen.ActiveControl.Text, "0"))
            
            Select Case Err.Number
                Case 0 'no error, calculation has evaluated to a number
                    Response = 0
                    SendKeys "{ENTER}"
                Case 2420, 2431, 2434, 2436, 2438, 2482 'unable to evaluate text in a numeric field to a number
                    MsgBox "Sorry - this entry does not evaluate to a number" & vbCrLf & "Please re-enter", vbOKOnly + vbExclamation, "Entry Error"
                    Response = 0 'so standard error message is not generated
                Case Else
            End Select
        
    End Select
  
 End Sub

To put this into a module,

Code:

 Function frm_errHandler(frm as form??, dataerr as integer???, Response as Integer??)
  
 ...
 code as above
 ...
  
 end function

I need to know the value for dataerr and send the response back. The question is - where do I get it from? I've tried passing the form through as a parameter, but cannot find the dataerr value within the form object.

Any suggestions?

gemma-the-husky · Sep 22, 2016

maybe the error code is a feature of the application object, or the dbengine, or something like that.

eg https://support.microsoft.com/en-us/kb/186063

not sure where to look, though. maybe it's not possible. eg How does setting the response to AcDataErrHandled (I think that's right offhand) suppress the underlying error. Is that a feature of the error event, or of the form itself responding to the flag within the form class?

jdraw · Sep 22, 2016

CJ,

There is one of these Global Error handlers that gets referenced from time to time. (vbWatchDog)
It is from EverythingAccess by Wayne Phillips. More info at his site

Also, I remember questions on the forum from md Lueck who was dealing with an error handler.
I found this just now
http://www.access-programmers.co.uk/forums/showthread.php?t=245942&highlight=lueck

Hope it's helpful.

CJ_London · Sep 22, 2016

Thanks both

since posting I had investigated dao.errors and dbengine.errors (haven't found anything under application) because form errors are typically dbengine errors. Both produced the same result

3265 Item not found in this collection.

which is a different error - 2113 is 'The value you entered isn't valid for this field'

I'll take a look at mdlueck's thread tomorrow - looks a possible

Thanks for the EverythingAccess link, I'll take a look at the trial - indicates it is possible but looks like I might need to dive off into C++ territory to do what I want which is probably more hassle than I need right now

isladogs · Aug 15, 2017

I'm a bit late to this party - just a year or so but then I hadn't joined AWF when it was written ....

Anyway, here's my take on this - which I realise doesn't really relate to the ideas in the original post

I use standard error handling for all code modules in forms & reports similar to this:

Code:

Private Sub Form_Load()

On Error GoTo Err_Handler

    ... other code here ...
    
Exit_Handler:
    Exit Sub

Err_Handler:
    'create error message & log
    strProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
    PopulateErrorLog
    Resume Exit_Handler
    
End Sub

NOTE:
1. I declare strProc as a global variable

Code:

Global strProc As String

2. The code requires the VBA reference 'Microsoft Visual Basic for Applications Extensibility 5.3'

The code line:

Code:

strProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)

This gets the name of the code module from the first line - in this case Form_Load

3. PopulateErrorLog is a function used to save a record of any program errors so these can be followed up & fixed.

As you can never expect clients to inform you if an error occurs, production versions also automatically send an email to me with the details of the error:
- user name; date/time; error number & description ; code module triggering the error

Obviously the error logging can be omitted in which case the error handler is changed to:

Code:

Err_Handler:
    'create error message & log
    strProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
    MsgBox "Error " & err.Number & " in " & strProc & " procedure : " & err.Description, vbExclamation, "Program Error"
    Resume Exit_Handler
    
End Sub

HTH

global form error handler (1 Viewer)

CJ_London

Super Moderator

gemma-the-husky

Super Moderator

jdraw

Super Moderator

CJ_London

Super Moderator

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread