Advanced error handling - passing varaibles from one module to another 1

[jordanking]

Hello,

I am developing some advanced error handling for a database. I have a sql server back end and a adp front end. I have built a function that takes error data and inserts it into a sql table for tracking. What I want to do is have a form open on non critical errors that the user can add notes or descriptions to the error tracking record.

The problem is: I am having trouble passing data between the two from modules.

from the documentation, the err object is cleared once you reach a end sub or end function. So I tried to create a public custom data type containing the error information. But it only existis within its module.

I want this error form to be able to be called from any other form in the adp.

so I created this code to catach an error and open the error form:

Private Sub cmdSearch_Click()
Dim iRet As Integer
On Error GoTo ErrLog
    
    'main code goes here
    ' Raise the overflow error
    err.Raise 6
Exit_Sub:
    Exit Sub
ErrLog:
    DoCmd.OpenForm "frmErrorLog"
    Resume Exit_Sub

End Sub

this opens the error form for logging errors. The global err object is still available to the first sub or function that executes on the newly opened form. I have code in the on open event:

Option Explicit

Public Type DMS_Error
    intVBAError As Integer
    chrVBADescription As String
    chrSource As String
    chrSQLState As String
    intNativeError As Integer
    chrNativeDescription As String
    intEmployeeID As Integer
    chrUserName  As String
    chrStation As String
    intRecType  As Integer
    intAction  As Integer
    intAuditID  As Integer
    chrNote As String
End Type

Private Sub cmdExit_Click()
    Dim strNotes As String
    Dim iRet As Integer
    
    strNotes = Nz(Me.chrNote, "")
    iRet = DMS_ErrorLog("sqlState", 0, "native Description", rtCustomer, atUpdate, 777, strNotes)
    Debug.Print "POINT 3 Err#: " & err.Number
    DoCmd.Close
End Sub


Private Sub Form_Open(Cancel As Integer)
    
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
Dim rsConfig As New ADODB.Recordset
Dim sqlConfig As String
Dim strVersion As String

    Me.lblNumber.Caption = err.Number
    Me.lblDescription.Caption = err.Description

    'initialise connection string and open connection
    cnn.ConnectionString = [Forms]!frmSwitchboard!xProvider & [Forms]!frmSwitchboard!xDataSource
    cnn.Open
    
    sqlConfig = "SELECT chrVersion FROM dbo.DMS_Config WHERE intConfigID = 1"
    rsConfig.Open sqlConfig, cnn, adOpenStatic
    Me.lblVersion.Caption = rsConfig.Fields("chrVersion")
    
    Me.lblCopy.Caption = Chr(169) & " 2005 - " & CStr(Format(Date, "yyyy"))
    Me.chrNote.SetFocus

    ' Close the connection.
    cnn.Close
    Debug.Print err.Number

End Sub

the err number is printed correctly. However, if the error was generated by an external application, say sql server, and it returned error information, I want to be able to assign that info the the err object, or create another custom object (ie: Public Type DMS_Error) with the same global scope of the err object. I could then assign the values of that object to hidden text fields on the form during the open event, which could then be refrenced by the
Private Sub cmdExit_Click(), which adds the users description to the information and initiates my error Record insert in sql server(DMS_ErrorLog)

or they may be a way easier way to accomplish all this.

Any help would be appreciated.

 

[HarleyQuinn]

This might be a bit overkill (or a good idea depending on how you look at it ) but how about building an error handling class?

You can use the number, name, calling function (or wherever it's come from) as properties and pass the class reference between forms using it just like the native err object?

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[jordanking]

thanks harley,

I had a feeling that is where this was going, but i have not created classes yet. Do you have a link to a good description to get started?

 

[HarleyQuinn]

Phew! That took longer than I thought to find an example on the 'net!

Here's a (very) basic link on what classes are and using them in Access.

Here's a link describing the use of a custom error class. Click on the "Passing Err Object" post and there's code in there.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[jordanking]

thanks harley,

that is enough to get me going,

I will let you know what I come up with, or more problems, ha

JK

 

[HarleyQuinn]

Cheers JK, glad I could help

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[jordanking]

SOLUTION:

my first experience creating a class. Wish I had learned this earlier, they are very powerful tools.

okay,

first here is the class code, called clsError

Option Compare Database
Option Explicit

Private intVBAError As Integer
Private chrVBADescription As String
Private chrSource As String
Private chrSQLState As String
Private intNativeError As Integer
Private chrNativeDescription As String
Private intRecType As DMS_RecType
Private intAction As DMS_Action
Private intAuditID As Integer
Private chrNote As String

''Get returns the value out of the class
Public Property Get NativeError() As Integer
    NativeError = intNativeError
End Property

''Let puts the value into the class
Public Property Let NativeError(Value As Integer)
    intNativeError = Value
End Property

Public Property Get VBAError() As Integer
    VBAError = intVBAError
End Property

Public Property Let VBAError(Value As Integer)
    intVBAError = Value
End Property

Public Property Get VBADescription() As String
    VBADescription = chrVBADescription
End Property

Public Property Let VBADescription(Value As String)
    chrVBADescription = Value
End Property

Public Property Get SQLState() As String
    SQLState = chrSQLState
End Property

Public Property Let SQLState(Value As String)
    chrSQLState = Value
End Property

Public Property Get NativeDescription() As String
    NativeDescription = chrNativeDescription
End Property

Public Property Let NativeDescription(Value As String)
    chrNativeDescription = Value
End Property

Public Property Get Note() As String
    Note = chrNote
End Property

Public Property Let Note(Value As String)
    chrNote = Value
End Property

''function returns a value
Public Function ErrorLogInsert() As Integer
    Dim iRet As Integer
    ''' call the function that inserts the error log into the SQL server database
    iRet = DMS_ErrorLog(intVBAError, chrVBADescription, chrSource, chrSQLState, intNativeError, chrNativeDescription, intRecType, intAction, intAuditID, chrNote)
    ErrorLogInsert = iRet
End Function

Public Sub ErrorObjInfo(err As ErrObject, RecType As DMS_RecType, Optional Action As DMS_Action, Optional AuditID As Integer)
    ''''get info for appropriate class variables
    intVBAError = err.Number
    chrVBADescription = err.Description
    chrSource = err.Source
    intRecType = RecType
    intAction = Nz(Action, 0)
    intAuditID = Nz(AuditID, 0)
End Sub

then i declared an instance of this class publically in a module unbound to any form:

Public DMS_Error As clsError

then i added code to the desired form that gets executed once there is an error

Private Sub cmdSearch_Click()
Dim iRet As Integer
On Error GoTo ErrLog

    'main code goes here (taken out for readablility)
    ' Raise a fake overflow error
    err.Raise 6
Exit_Sub:
    Exit Sub
ErrLog:
    Set DMS_Error = New clsError
    err.Source = Me.Module.Name
    DMS_Error.ErrorObjInfo err, rtCustomer ''include error object and optional record type, action and auditid
    DoCmd.OpenForm "frmErrorLog"
    Resume Exit_Sub

End Sub

then I made a form to display the error info and allow for a user to add thier own notes/description

Option Compare Database
Option Explicit

Private Sub cmdExit_Click()

On Error GoTo ErrorHandler
    
    Dim strNotes As String
    Dim iRet As Integer
    If Len(Nz(Me.chrNote, "" & "")) > 0 Then
        DMS_Error.Note = Trim(Me.chrNote)
    End If
    iRet = DMS_Error.ErrorLogInsert
    DoCmd.Close

Exit_Sub:
    Exit Sub

ErrorHandler:
    MsgBox "The following Error Occured while inserting an Error Log:" & Chr(13) & Chr(13) & "DESCRIPTION: " & err.Description & Chr(13) & "NUMBER:" & err.Number & Chr(13) & Chr(13) & "The current action will be canceled.  Please contact the System Administrator", vbOKOnly + vbCritical + vbApplicationModal + vbMsgBoxSetForeground, "DMS CRITICAL ERROR"
    Resume Exit_Sub
    
End Sub

Private Sub Form_Open(Cancel As Integer)

On Error GoTo ErrorHandler
    
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    Dim rsConfig As New ADODB.Recordset
    Dim sqlConfig As String
    Dim strVersion As String

    Me.lblNumber.Caption = DMS_Error.VBAError
    Me.lblDescription.Caption = DMS_Error.VBADescription

    'initialise connection string and open connection
    cnn.ConnectionString = [Forms]!frmSwitchboard!xProvider & [Forms]!frmSwitchboard!xDataSource
    cnn.Open
    
    sqlConfig = "SELECT chrVersion FROM dbo.DMS_Config WHERE intConfigID = 1"
    rsConfig.Open sqlConfig, cnn, adOpenStatic
    Me.lblVersion.Caption = rsConfig.Fields("chrVersion")
    
    Me.lblCopy.Caption = Chr(169) & " 2005 - " & CStr(Format(Date, "yyyy"))
    Me.chrNote.SetFocus

    ' Close the connection.
    cnn.Close

Exit_Sub:
    Exit Sub

ErrorHandler:
    MsgBox "The following Error Occured while inserting an Error Log:" & Chr(13) & Chr(13) & "DESCRIPTION: " & err.Description & Chr(13) & "NUMBER:" & err.Number & Chr(13) & Chr(13) & "The current action will be canceled.  Please contact the System Administrator", vbOKOnly + vbCritical + vbApplicationModal + vbMsgBoxSetForeground, "DMS CRITICAL ERROR"
    Resume Exit_Sub

End Sub

so once the user closes this form, the class function is called that adds the user's description to he error info and inserts a record into the SQL server back end.

i will include the code that inserts the record into sql server in order to be thorough

''Custom created Public functions
''Created by Jordan King

Option Compare Database
Option Explicit

'''Action Types
Public Enum DMS_Action
     atInsert = 1
     atUpdate = 2
     atDelete = 5
     atImport = 6
     atExport = 7
     atCascadeUpdate = 8
     atCascadeDelete = 9
     atInvoice = 10
     atSearch = 11
End Enum

'''Record Types
Public Enum DMS_RecType
    rtCustomer = 1
    rtCompany = 2
    rtResidential = 3
    rtCommercial = 4
    rtPurchase = 5
    rtProduct = 6
    rtCredit = 7
    rtSDB = 8
    rtERUser = 9
    rtERRecord = 10
    rtAudit = 11
    rtProdDetial = 12
End Enum

Public DMS_Error As clsError

Public Function DMS_ErrorLog(intVBAError As Integer, chrVBADescription As String, chrSource As String, Optional chrSQLState As String, Optional intNativeError As Integer, Optional chrNativeDescription As String, Optional intRecTp As DMS_RecType, Optional intAction As DMS_Action, Optional intAuditID As Integer, Optional chrNote As String) As Integer

'On Error GoTo ErrorHandler

    Dim cnn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim prm As New ADODB.Parameter
    
    '''''''''''connect to SQL server
    cnn.ConnectionString = [Forms]!frmSwitchboard!xProvider & [Forms]!frmSwitchboard!xDataSource
    cnn.Open
    Set cmd.ActiveConnection = cnn
    
    ''''''''''Set comand type and text
    cmd.CommandText = "spError_INSERT_New"
    cmd.CommandType = adCmdStoredProc
    cmd.NamedParameters = True
    
    '''explicitly create the input parameters
    Set prm = cmd.CreateParameter("@intVBAError", adInteger, adParamInput, , intVBAError)
    cmd.Parameters.Append prm
    Set prm = cmd.CreateParameter("@chrVBADescription", adChar, adParamInput, Len(chrVBADescription), chrVBADescription)
    cmd.Parameters.Append prm
    Set prm = cmd.CreateParameter("@chrSource", adChar, adParamInput, Len(chrSource), chrSource)
    cmd.Parameters.Append prm
    If Len(Nz(chrSQLState, "")) > 0 Then
        Set prm = cmd.CreateParameter("@chrSQLState", adChar, adParamInput, Len(chrSQLState), chrSQLState)
        cmd.Parameters.Append prm
    End If
    If Nz(intNativeError, 0) > 0 Then
        Set prm = cmd.CreateParameter("@intNativeError", adInteger, adParamInput, , intNativeError)
        cmd.Parameters.Append prm
    End If
    If Len(Nz(chrNativeDescription, "")) > 0 Then
        Set prm = cmd.CreateParameter("@chrNativeDescription", adChar, adParamInput, Len(chrNativeDescription), chrNativeDescription)
        cmd.Parameters.Append prm
    End If
    Set prm = cmd.CreateParameter("@intEmployeeID", adInteger, adParamInput, , CInt([Forms]!frmSwitchboard!intEmployee))
    cmd.Parameters.Append prm
    Set prm = cmd.CreateParameter("@chrUserName", adChar, adParamInput, Len(Trim([Forms]!frmSwitchboard!chrUser)), Trim([Forms]!frmSwitchboard!chrUser))
    cmd.Parameters.Append prm
    Set prm = cmd.CreateParameter("@chrStation", adChar, adParamInput, Len(Trim([Forms]!frmSwitchboard!chrStation)), Trim([Forms]!frmSwitchboard!chrStation))
    cmd.Parameters.Append prm
    If Nz(intRecTp, 0) > 0 Then
        Set prm = cmd.CreateParameter("@intRecType", adInteger, adParamInput, , intRecTp)
        cmd.Parameters.Append prm
    End If
    If Nz(intAction, 0) > 0 Then
        Set prm = cmd.CreateParameter("@intAction", adInteger, adParamInput, , intAction)
        cmd.Parameters.Append prm
    End If
    If Nz(intAuditID, 0) > 0 Then
        Set prm = cmd.CreateParameter("@intAuditID", adInteger, adParamInput, , intAuditID)
        cmd.Parameters.Append prm
    End If
    If Len(Nz(chrNote, "")) > 0 Then
        Set prm = cmd.CreateParameter("@chrNote", adChar, adParamInput, Len(chrNote), chrNote)
        cmd.Parameters.Append prm
    End If
    '''explicitly create the output parameters
    Set prm = cmd.CreateParameter("@intErrorID", adInteger, adParamOutput)
    cmd.Parameters.Append prm
    Set prm = cmd.CreateParameter("@ReturnValue", adInteger, adParamOutput)
    cmd.Parameters.Append prm
    Set prm = cmd.CreateParameter("@LocalError", adInteger, adParamOutput)
    cmd.Parameters.Append prm
    Set prm = cmd.CreateParameter("@LocalRows", adInteger, adParamOutput)
    cmd.Parameters.Append prm

    'execute the command
    cmd.Execute
    cmd.CommandText = ""

    'return the results/output
    If IsEmpty(cmd.Parameters("@ReturnValue").Value) Or cmd.Parameters("@ReturnValue").Value <> 0 Then
        MsgBox "Error Log Failed" & Chr(13) & "Error Number: " & cmd.Parameters("@LocalError").Value, vbOKOnly + vbExclamation, "DMS Error Log"
    Else
        ''' return the resutls from the parpameter
    End If

    ' Close the connection.
    cnn.Close

Exit_Sub:
    Exit Function

ErrorHandler:
    MsgBox "The following Error Occured while inserting an Error Log:" & Chr(13) & Chr(13) & "DESCRIPTION: " & err.Description & Chr(13) & "NUMBER:" & err.Number & Chr(13) & Chr(13) & "The current action will be canceled.  Please contact the System Administrator", vbOKOnly + vbCritical + vbApplicationModal + vbMsgBoxSetForeground, "DMS CRITICAL ERROR"
    Resume Exit_Sub

End Function

and to take it one step further, just for anyone who might want to know,

the following is the stored procedure that is called in SQL server:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spError_INSERT_New]
	-- Add the parameters for the stored procedure here
	@intVBAError int, 
	@chrVBADescription nvarchar(50) = Null,
	@chrSource nvarchar(50) = Null,
	@chrSQLState nvarchar(5) = Null,
	@intNativeError int = Null,
	@chrNativeDescription nvarchar(50) = Null,
	@intEmployeeID int = Null,
	@chrUserName nvarchar(50) = Null,
	@chrStation nvarchar(500) = Null,
	@intRecType int = Null,
	@intAction int = Null,
	@intAuditID int = Null,
	@chrNote nvarchar(50) = Null,
	@intErrorID int = 0 OUTPUT,
	@ReturnValue int = 0 OUTPUT,
	@LocalError int = 0 OUTPUT,
	@LocalRows int = 0 OUTPUT

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Insert DECLARE statements for variables here
	DECLARE 
		@dtCreated datetime

	SELECT @dtCreated = GETDATE()

	-- Begin Transaction
	BEGIN TRANSACTION	
    -- Insert statements for procedure here
	INSERT INTO DMS_Error
	(intVBAError, chrVBADescription, chrSource, chrSQLState, intNativeError, chrNativeDescription, intEmployeeID, chrUserName, chrStation,	intRecType, intAction, intAuditID, chrNote, dtCreated)
	VALUES
	(@intVBAError, @chrVBADescription, @chrSource, @chrSQLState, @intNativeError, @chrNativeDescription, @intEmployeeID, @chrUserName, @chrStation, @intRecType, @intAction, @intAuditID, @chrNote, @dtCreated)
	-- Evaluate if there was an error with the preceeding insert action
	SELECT @LocalError = @@ERROR, @LocalRows = @@ROWCOUNT
	-- If either LocalError is not 0 or LocalRows is 0, the statement failed	
	IF NOT @LocalError = 0 or @LocalRows = 0  
		BEGIN
		ROLLBACK TRANSACTION
		SELECT @intErrorID = Null, @ReturnValue = 1
		SELECT @intErrorID, @LocalError, @LocalRows, @ReturnValue
		END
	ELSE
		BEGIN
		COMMIT TRAN	
		SELECT @intErrorID = @@IDENTITY, @ReturnValue = 0
		SELECT @intErrorID, @LocalError, @LocalRows, @ReturnValue
	END
END

So.....
in the end I get a log of every error that occurs that is not fatal. I needed this because I am not "on location" every day in order to trouble shoot errors. This database is located on a windows server environment. So i also log the windows username, worksation, and employee signed into the access project front end. In the end I have a very comprehensive look at where the error came from.

thanks everyone for your help.

JK

 

[jordanking]

sorry, one more note,

I created the form to be unbound and not use an ODBC connection to SQL server 2005. SO the connection objects have to be explicitly created for each connection to the server, the connection data is stored in hidded contorls on the main forms in the access front end so they could be changed easily at a later date. They are xProvider, and xDataSource

I include all of this becasue I found almost no example online of how to run an unbound form from an access project front end SQL server 2005 back end.