Event Logger Generation - Calling function w/Parameters from within another function

I have code that will update SQL tables and uses a Stored Procedures to gather the information.  The following code works successfully and updates the SQL table.

I am looking for assistance in now using these functions to capture information regarding the processeing of the databases.   I need to capture the rowcount of each query being used with the function, the Function/Procedure Name, Error handling etc.

I am unsure on where to place the CALL of the function and the proper syntax to capture the necessary information.

The following is the Event handling Code/Classes.

Option Compare Database
Option Explicit

'Compiled in Access 2003, 04-Sept-2013
'REFERENCES NEEDED:
'
'Visual Basic For Applications
'Microsoft Access 11.0 Object Library
'Microsoft DAO 3.6 Object Library
'OLE Automation
'Microsoft ActiveX Data Objects 2.8 Library

Private Const sproc As String = "ISCenter_Monitor.usp_log_ISCenter_Event"
Private Const connstr = _
    "Driver={SQL Server};Server=AQL02;database=TESTDB;UID=******;PWD=****"


Public Sub EventLog_Open_And_Close_Using_Class()
    On Error GoTo PROC_ERROR
    
    Dim objLog As New clsISCenterLogger_EventLog
    
    'Set the ThrowErrors property to one of the following values when running under automation:
    '   eIgnoreErrors
    '   eThrowOnly
    '
    
    objLog.ThrowErrors = eRaiseOnly
        
    If objLog.IsOpen = False Then objLog.OpenLogRecord
    
    If objLog.IsOpen = True Then _
        objLog.CloseLogRecord RowsAffected:=999, _
                              ErrMsg:="EventLog Error Message", _
                              AdditionalInfo:="EventLogged using VBA class", _
                              StepSucceeded:=1
                          
                          
    
    GoTo PROC_EXIT
    
PROC_ERROR:
    MsgBox err.Description
    Resume PROC_EXIT
    
PROC_EXIT:
    
End Sub
Private Sub EventLog_Open_And_Close()
    On Error GoTo PROC_ERROR
    
    Dim eventid As Long
    Dim RowsAffected As Long
    Dim ErrorMessage As String
    Dim AdditionalInfo As String
    Dim StepSucceeded As Integer
    
    RowsAffected = 999
    ErrorMessage = "EventLog Error Message #003"
    AdditionalInfo = "EventLoging from MS Access VBA/ADODB"
    StepSucceeded = 1
    
    eventid = EventLog_open_log_record()
    If eventid <> 0 Then EventLog_close_log_record eventid, RowsAffected, _
        ErrorMessage, AdditionalInfo, StepSucceeded
    MsgBox "EventID was " + CStr(eventid)
    
    
    GoTo PROC_EXIT
    
PROC_ERROR:
    MsgBox err.Description
    Resume PROC_EXIT
    
PROC_EXIT:
    
End Sub

Private Function EventLog_open_log_record() As Long
    On Error GoTo PROC_ERROR
    
    Dim conn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    'Dim parm As ADODB.Parameter
    Dim eventid As Long
    eventid = 0
    
    conn.ConnectionString = connstr
    
    conn.Open
    
    With cmd
        .ActiveConnection = conn
        .CommandType = adCmdStoredProc
        .CommandText = sproc
        .NamedParameters = True
    
        'Dim pEventLog As ADODB.Parameter
        'For Each pEventLog In cmd.Parameters
        '    Debug.Print pEventLog.Name
        'Next pEventLog
        
        .Parameters("@EventName").value = "EventLog Event"
        .Parameters("@ModuleName").value = "EventLog Module"
        .Parameters("@ProcedureName").value = "EventLog Procedure"
    End With
    
    'Set parm = cmd.CreateParameter("@ReturnCode", adInteger, , adParamReturnValue)
    'cmd.Parameters.Append parm
    
    cmd.Execute
    eventid = cmd.Parameters("@RETURN_VALUE").value
    
    GoTo PROC_EXIT
    
PROC_ERROR:
    MsgBox err.Description
    Resume PROC_EXIT
    
PROC_EXIT:
    On Error Resume Next
    conn.Close
    EventLog_open_log_record = eventid
    
End Function

Private Sub EventLog_close_log_record(eventid As Long, RowsAffected As Long, ErrMsg _
    As String, AdditionalInfo As String, StepSucceeded As Integer)
    On Error GoTo PROC_ERROR
   
    Dim conn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    
    conn.ConnectionString = connstr
    conn.Open
    
    With cmd
        .ActiveConnection = conn
        .CommandType = adCmdStoredProc
        .CommandText = sproc
        .NamedParameters = True
    
        .Parameters("@EventID").value = eventid
        .Parameters("@ErrorMessage").value = ErrMsg
        .Parameters("@StepSucceeded").value = StepSucceeded
        .Parameters("@AffectedRows").value = RowsAffected
        .Parameters("@AdditionalInfo").value = AdditionalInfo
    End With
    
    cmd.Execute
    
    GoTo PROC_EXIT
    
PROC_ERROR:
    MsgBox err.Description
    Resume PROC_EXIT
    
PROC_EXIT:
    On Error Resume Next
    conn.Close
    
End Sub

Open in new window


The following is the function I am wanting to modify to include the event logger code:
Public Const csPathEDIInvoices = "C:\Development\InvoiceLoadTest" 
Public Const csPathImportData = "C:\Development\InvoiceLoadTest" 

Public Sub InvoicesLoad(Optional ByVal JobList As Form_frmjobs = Nothing, _
                                    Optional ByVal Confirm As Boolean = True, _
                                    Optional ByVal RptName As String = "", _
                                    Optional ByVal rptDesc As String = "")

    Const ProcName = "InvoicesLoad"

    ''  07/25/2008  49rsc   added logging
    ''  02/12/2010  49rsc   Call InvoicesLoad_RunSQLAgentJob

    Dim ReportName    As String
    Dim ReportDesc    As String

    On Error GoTo ErrorHandler

    If RptName <> "" Then
        ReportName = RptName
        ReportDesc = rptDesc
    Else
    ReportName = JobList.CurrentJob
    ReportDesc = JobList.CurrentJobDesc
    End If

    Dim dbs As Database
    Dim rs As Recordset
    Set dbs = CurrentDb
    Set rs = dbs.OpenRecordset("tblSelectionMenu")

    rs.FindFirst "RptName = '" & ReportName & "'"
    Dim Msg, Desc As String
    Msg = "Do you want to proceed with your selection: " & ReportName & "?"
    Desc = Msg & vbCrLf & vbCrLf & "REPORT/TOOL DESCRIPTION:" & vbCrLf & rs.Fields("Description")

    If Confirm Then
    If MsgBox(Desc, vbYesNo, "Update/Report Selection") = vbNo Then
    Exit Sub
    End If
    End If

    ' 3/31/05 49mam: Change SendKeys to Automation
    '---------------------------------------------
    Dim xla       As Excel.Application
    Dim xlb       As Excel.Workbook
    Dim xls       As Excel.Worksheet
    Dim sFiles    As String ' source filename
    Dim sFileD    As String ' destination filename
    Dim StartTime As Date
    Dim EndTime   As Date
    Dim RunTime   As String

    Const cstrImportTableName   As String = "tblInvoices_Import"
    Const cstrAccumulatorName   As String = "tblInvoicesData"
    Dim lngRC_Import        As Long
    Dim lngRC_BeforeAppend  As Long
    Dim lngRC_AfterAppend   As Long
    Dim sErr                As String

    StartTime = Time

    sFiles = csPathEDIInvoices & Format(date, "YYYYMMDD") & " EDIInvoices.xls"
    sFileD = csPathImportData & "InvoiceData.xls"

    ' delete destination file, if it exists
    On Error Resume Next
    Kill sFileD
    On Error GoTo ErrorHandler

    ' copy source file to destination file
    If Dir(sFiles) = "" Then
        err.Raise Number:=clErrBoeingInvoicesNoData, _
                  Description:=Replace(csErrBoeingInvoicesNoData, "%srcfile%", sFiles)
    End If
    On Error Resume Next
    FileCopy sFiles, sFileD
    If err Then
        sErr = csErrFileCopy
        sErr = Replace(sErr, "%source%", sFiles)
        sErr = Replace(sErr, "%dest%", sFileD)
        err.Raise Number:=clErrFileCopy, _
                  Description:=sErr
    End If
    On err GoTo ErrorHandler

    ' launch Excel and open destination file
    Set xla = New Excel.Application
    Set xlb = xla.Workbooks.Open(FileName:=sFileD)
    Set xls = xlb.Worksheets(1)

    ' format report
    '====================================================================
    ' CODE CHANGE 18-July-2012 by 49mwg

    FormatReport_ThrowErrors (xls)
    'Call FormatReport(xls)
    '====================================================================

    ' re-format column "BH" (format as Date and remove Time portion from cell values)
    With xls.Range("BH:BH")
    .NumberFormat = "mm/dd/yy"
    .Replace " *", ""
    End With

    ' save and close
    xlb.Save
    xlb.Close False
    xla.Quit
    '---------------------------------------------

    DoCmd.SetWarnings (False)
    DoCmd.OpenQuery ("qryCLEAR:Invoices_Import")
    'Imports S:\49bse\Invoices Sent To Boeing\Edi\InvoiceUpdate.xls
    DoCmd.TransferSpreadsheet acImportDelim, , "tblInvoices_Import", csPathImportData & "InvoiceData.xls", True

    mWriteLog_RecordCounts cstrImportTableName, "Records imported", lngRC_Import
    mWriteLog_RecordCounts cstrAccumulatorName, "Accumulator records before append", lngRC_BeforeAppend

    'Appends imported data into the database
    DoCmd.OpenQuery ("qryAPPEND:InvoiceData")

    mWriteLog_RecordCounts cstrAccumulatorName, "Accumulator records after append", lngRC_AfterAppend

    ''  02/12/2010
    Call InvoicesLoad_SQLJob_InvoiceData2

    DoCmd.SetWarnings (True)

    'Logs update time in tblUpdateLog
    Dim rs1, rs2 As Recordset
    Set rs1 = dbs.OpenRecordset("tblUpdateLog")
    Set rs2 = dbs.OpenRecordset("tblInvoices_Import")
    rs1.Edit
    rs1.Fields("Invoices") = date
    rs1.Update
    Dim ct As String
    rs2.MoveLast
    ct = Format(rs2.RecordCount, "###,###")
    EndTime = Time
    RunTime = (EndTime - StartTime)
    RunTime = Format(RunTime, "hh:mm:ss")

    Dim rs3 As Recordset
    Set rs3 = dbs.OpenRecordset("tblDailyLog")
    rs3.FindFirst "Name = '" & ReportName & "'"
    rs3.Edit
    rs3.Fields("UpdateDate") = date
    rs3.Fields("RunTime") = RunTime
    rs3.Update

    If Confirm Then SafeMsgBox "Update complete." & vbCrLf & vbCrLf & ct & " records imported." & vbCrLf & "Run Time: " & RunTime, , "Database Information"

ExitHandler:
    On Error Resume Next

    xlb.Close False
    xla.Quit

    Set xls = Nothing
    Set xlb = Nothing
    Set xla = Nothing

    Exit Sub

ErrorHandler:
    DoCmd.Hourglass (False)
    DoCmd.SetWarnings (True)

    sErr = "InvoicesLoad() -- " & err.Number & ". " & err.Description

    Call HandleError(ErrNumber:=err.Number, _
                                    ErrDescription:=err.Description, _
                                    ErrTitle:=err.source, _
                                    module:=MODNAME, _
                                    Procedure:=ProcName)

    mWriteLogIndent 2, sErr

    Resume ExitHandler
End Sub

Open in new window


Stored Procedure

USE [TRACI_ANALYTICS]
GO
/****** Object:  StoredProcedure [ISCenter_Monitor].[usp_log_ISCenter_Event]    Script Date: 10/04/2013 13:27:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [ISCenter_Monitor].[usp_log_ISCenter_Event]  
(
	  @EventID				integer			= NULL 
	, @EventName			nvarchar(255)	= NULL 
	, @ModuleName			nvarchar(255)	= NULL 
	, @ProcedureName		nvarchar(255)	= NULL 
	, @EventStep			nvarchar(255)	= NULL 
	, @EventStepParentID	integer			= NULL 
	, @AffectedRows			integer			= NULL 
	, @StepSucceeded		bit				= NULL 
	, @ErrorMessage			nvarchar(512)	= NULL 
	, @AdditionalInfo		nvarchar(1024)	= NULL 
)
AS BEGIN 


--========================================================================================
--
-- SCRIPT:      usp_log_ISCenter_Event
--
-- AUTHOR:      --
-- DESCRIPTION:  Logs an event to ISCenter_Monitor].[ISCenter_EventLog]
--
-- The following parameters are used only when an event entry is being created. 
-- These parameters will be ignored when closing an event; no error will be raised.  
-- (@EventID is NULL):
--	 @EventName
--	 @ModuleName
--	 @ProcedureName
--	 @EventStep
--	 @EventStepParentID
--
-- CHANGE HISTORY
-- DATE		    BY      ISSUE #          DESCRIPTION
-- ------------ ------- ---------------  -----------------------------------------------------------
-- 27-June-2013 49mwg					 Created sproc. 
-- 13-Sept-2013 49mwg					 Update [AdditionalInfo]
--
--========================================================================================

	
SET NOCOUNT ON 
	
DECLARE @OpenEventID	integer
DECLARE @EndDate		datetime 
DECLARE @RETVAL			integer

BEGIN TRY 

SET @RETVAL = 0 

IF @EventID IS NULL 
BEGIN 
	-- Creating a new event log entry
	-- If @EventStepParentID is provided, verify that the ParentID exists. 
	IF @EventStepParentID IS NOT NULL 
	BEGIN 
		SELECT	@OpenEventID = EventID 
		FROM	[ISCenter_Monitor].[ISCenter_EventLog] 
		WHERE	EventID = @EventStepParentID 
	
		IF @OpenEventID IS NULL 
		BEGIN 
			RAISERROR   (  N'A parent event was specified (EventID = %d), but no matching EventID was found in the event table.'  
						 , 11		-- severity 
						 , 1		-- state
						 , @EventStepParentID 
						)	
		END 
	END 
	
	BEGIN 
		INSERT INTO	[ISCenter_Monitor].[ISCenter_EventLog]  
			(EventName, EventStartDate, ModuleName, ProcedureName, EventStep, EventStepParentID) 
		SELECT    @EventName 
				, GETDATE() 
				, @ModuleName 
				, @ProcedureName 
				, @EventStep 
				, @EventStepParentID 
				
		SET @RETVAL = SCOPE_IDENTITY()
	END 
	
		RAISERROR   (  'Test Error - Thrown intentionally!'  
					 , 11		-- severity 
					 , 1		-- state
					 , @EventID  
					)
	END 

ELSE BEGIN 
	-- Closing an existing event log entry
	-- Verify that the specified @EventID exists. 
	
	IF @StepSucceeded IS NULL 
	BEGIN 
		RAISERROR   (  N'@StepSucceeded is NULL. You must specify SUCCEED (1) or FAIL (0).'  
					 , 11		-- severity 
					 , 1		-- state
					 , @EventID  
					)
	END 
	
	SELECT	@OpenEventID	= EventID, 
			@EndDate		= EventEndDate 
	FROM	[ISCenter_Monitor].[ISCenter_EventLog] 
	WHERE	EventID = @EventID 
	
	IF @OpenEventID IS NULL 
	BEGIN 
		RAISERROR   (  N'The specified event (EventID = %d) was not found in the event table.'  
					 , 11		-- severity 
					 , 1		-- state
					 , @EventID  
					)
	END 
		 
	IF @EndDate IS NOT NULL
	BEGIN 
		RAISERROR   (  N'The specified event (EventID = %d) already has an end date specified. This event may not be updated.'  
					 , 11		-- severity 
					 , 1		-- state
					 , @EventID  
					)
	END 
		 
	UPDATE	[ISCenter_Monitor].[ISCenter_EventLog]  
	SET		  EventEndDate		= GETDATE() 
			, AffectedRows		= @AffectedRows 
			, StepSucceeded		= @StepSucceeded 
			, ErrorMessage		= @ErrorMessage 
			, AdditionalInfo	= @AdditionalInfo 				
	WHERE	EventID = @EventID 				

		RAISERROR   (  'Test Error - Thrown intentionally!'  
					 , 11		-- severity 
					 , 1		-- state
					 , @EventID  
					)
	
END 

END TRY 


BEGIN CATCH
    DECLARE @ErrorMsg NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMsg = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMsg, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );

	RETURN -1 
END CATCH 

RETURN @RETVAL  

SET NOCOUNT OFF 

END 

Open in new window

Again, I am looking for help with calling the EventLogger code and including the correct paramenters to be passed to the SQL Stored procedure.  

Note:  the code EventLogger code is currently set to deliver a failed msg dialog.

Thanks,

karen
Karen SchaeferBI ANALYSTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Karen SchaeferBI ANALYSTAuthor Commented:
Still Looking for assistance.
0
Nick67Commented:
In some way, karen, you should have started the question it as a related question.
Then everyone who chimed in on the last one would get a ping.  Done deal now

Ok, we've built the sprocs and code to knock in error logging
Is this what calls it?

    Call HandleError(ErrNumber:=err.Number, _
                                    ErrDescription:=err.Description, _
                                    ErrTitle:=err.source, _
                                    module:=MODNAME, _
                                    Procedure:=ProcName)
Or is meant to invoke it?
0
Karen SchaeferBI ANALYSTAuthor Commented:
expect it is not error handling.  I want to be able to capture certain aspects as the code is being processed.  More of a log of activities within a function/process.

ie.  Query1 runs, Capture the recordcount of the appended table.
       Table Name, Module Name, Procedure Name, etc.

Then move on to the next step in the Function that will have some form of metrics to capture.

I am looking for the proper syntax for calling the function to capture the specified metrcs that are used within the SP.

Do I need to modify the function to capture the array of objects to be used as parameters/metrics?  (EventLog_Open_And_Close_Using_Class)

Note:  I already have Function that captures the recordcount, however, I am unsure on the proper syntax to use 2 function within 1 Call

ie.    Call EventLog_Open_And_Close_Using_Class(mWriteLog_RecordCounts(cstrImportTableName, "Records imported", lngRC_Import))



Thanks,
K
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Nick67Commented:
I am unsure on the proper syntax to use 2 function within 1 Call

...
Call MultiStageSub()
...

Private Sub MultiStageSub()
dim SomeItem as Whatever
SomeItem  =  SomeFunction()
dim AnotherItem as Whatever
AnotherItem =  SomeOtherFunction()
Call SomeProcedure
Call AnotherProcedure
End Sub

Using Call, the calling procedure is halted until the called procedure is completed in its entirety.  Depending on your need, you could put multiple Calls within the procedure you are logging, or you could break out everything that needs to happen to log correctly into its own procedure and then make just a single call.

I still am not quite sure what you are asking though--I am guessing, since you have a class, that you are looking at how to instantiate the class, and feed it the required parameters to get a logging event done

Dim EventLogger as New TheNameOfTheEventLoggerClass
EventLogger.SomeParameter = Something
EventLogger.SomeOtherParameter = SomethingElse
...
EventLogger.SomeMethodThatGetsThingsDone
Set EventLogger = Nothing

I'd probably wrap that in a sub, and feed the parameters in
...
Call LogIt(Something, SomethingElse)
...

Private sub LogIt(Something as whatever, SomethingElse as whatever)
Dim EventLogger as New TheNameOfTheEventLoggerClass
EventLogger.SomeParameter = Something
EventLogger.SomeOtherParameter = SomethingElse
...
EventLogger.SomeMethodThatGetsThingsDone
Set EventLogger = Nothing
end sub
0
Nick67Commented:
And I am confused
Public Sub EventLog_Open_And_Close_Using_Class()
is your sub, so
ie.    Call EventLog_Open_And_Close_Using_Class(mWriteLog_RecordCounts(cstrImportTableName, "Records imported", lngRC_Import))

is DEFINITELY no good right now, as EventLog_Open_And_Close_Using_Class presently doesn't take or require parameters.  Within it you instantiate a new class
clsISCenterLogger_EventLog
But I don't see code for that class.  I take it that it has some Get and Let methods for the sprocs parameters, and then calls the sproc?  You may need to post the code for the class!
0
Karen SchaeferBI ANALYSTAuthor Commented:
Here is the class named "clsISCenterLogger_EventLog"

Option Compare Database
Option Explicit

Const MODNAME = "clsISCenterLogger"

Const defConnString As String = _
    "Driver={SQL Server};Server=AQL02;database=TRACI_ANALYTICS;UID=****;PWD=****"
Const defSproc As String = "ISCenter_Monitor.usp_log_ISCenter_Event"

Private eventid As Long
Private cmdstr As String
Private ethrowerrors As eErrorHandlerMode

Public Enum eErrorHandlerMode
    eIgnoreErrors = 0
    eRaiseAndThrow = 1
    eRaiseOnly = 2
    eThrowOnly = 3
End Enum

'ADODB variables
Private conn As ADODB.Connection
Private cmd As ADODB.Command

' Note that the ConnectionString property uses the [conn] object,
' which was instantiated in the constructor (the class INITIALIZE() event.)

Property Let ThrowErrors(eParm As eErrorHandlerMode)
    ethrowerrors = eParm
End Property

Property Get ThrowErrors() As eErrorHandlerMode
    ThrowErrors = ethrowerrors
End Property

Property Let ConnectionString(pCS As String)
    conn.ConnectionString = pCS
End Property

Property Get ConnectionString() As String
    ConnectionString = conn.ConnectionString
End Property

Property Get IsOpen() As Boolean
    IsOpen = IIf(eventid = 0, False, True)
End Property

Public Sub ErrorEventLog()
    On Error GoTo PROC_ERROR
    err.Raise 10001, "error source", "This is a EventLog error"
    
    GoTo PROC_EXIT
    
PROC_ERROR:
    Call ErrorHandler(err, conn)
    Resume PROC_EXIT
    
PROC_EXIT:
    
End Sub

Private Sub Class_Initialize()
    'Constructor; called whenever you initialize an object of this class.
    Const ProcName = "Class_Initialize"
    
    On Error GoTo PROC_ERROR
    
    ' Initialize variables. Note that we set a default connection string, to save you the bother
    ' of setting it each time you instantiate this object. You can, of course, set it to
    ' something different.
    '
    ' If VBA implemented fully-functional objects, the constructor could accept parameters,
    ' and automatically establish a connection and open a log record at this time.
    ' Even better, the constructor could accept ConnectionString as a parameter, allowing you
    ' to specify the connection string when you create the object. Like this:
    '
    '   Dim objISlog as new clsISCenterLogger(myConnectionString)
    '
    ' That is how you would do it in C#. But VBA does not allow this. Pity.
    '
    
    eventid = 0
    cmdstr = defSproc
    ethrowerrors = eErrorHandlerMode.eIgnoreErrors
    
    Set conn = New ADODB.Connection
    Set cmd = New ADODB.Command
    
    conn.ConnectionString = defConnString
    
    'cmd.ActiveConnection = conn
    'cmd.CommandText = defSproc
    'cmd.CommandType = adCmdStoredProc
    'cmd.NamedParameters = True
    
    GoTo PROC_EXIT
    
PROC_ERROR:
    Call ErrorHandler(err, conn)
    Resume PROC_EXIT
    
PROC_EXIT:

End Sub

Public Function OpenLogRecord() As Boolean
    Const ProcName = "OpenLogRecord"
    On Error GoTo PROC_ERROR
    
    Dim retcode As Boolean
    
    If eventid > 0 Then err.Raise 10001, MODNAME & "::" & ProcName, _
        "Method OpenLogRecord() may not be called when a log record is already open."
    retcode = True
    conn.Open
    
    With cmd
        .ActiveConnection = conn
        .CommandType = adCmdStoredProc
        .CommandText = cmdstr
        .NamedParameters = True
    
        'Dim pEventLog As ADODB.Parameter
        'For Each pEventLog In cmd.Parameters
        '    Debug.Print pEventLog.Name
        'Next pEventLog
        
        .Parameters("@EventName").value = "EventLog Event"
        .Parameters("@ModuleName").value = "EventLog Module"
        .Parameters("@ProcedureName").value = "EventLog Procedure"
        
        .Execute
    End With
    
    eventid = cmd.Parameters("@RETURN_VALUE").value
    retcode = True
    GoTo PROC_EXIT
    
PROC_ERROR:
    Call ErrorHandler(err, conn)
    retcode = False
    Resume PROC_EXIT
    
PROC_EXIT:
    On Error Resume Next
    If conn.State <> ObjectStateEnum.adStateClosed Then conn.Close
    
End Function

Public Function CloseLogRecord(RowsAffected As Long, ErrMsg As String, _
    AdditionalInfo As String, StepSucceeded As Integer) As Boolean
    Const ProcName = "CloseLogRecord"
    On Error GoTo PROC_ERROR
   
    Dim retcode As Boolean
    
    If eventid <= 0 Then err.Raise 10002, MODNAME & "::" & ProcName, _
        "Method CloseLogRecord() may not be called when there is no log record open."
       
    retcode = True
    conn.Open
    
    With cmd
        .ActiveConnection = conn
        .CommandType = adCmdStoredProc
        .CommandText = cmdstr
        .NamedParameters = True
    
        .Parameters("@EventID").value = eventid
        .Parameters("@ErrorMessage").value = ErrMsg
        .Parameters("@StepSucceeded").value = StepSucceeded
        .Parameters("@AffectedRows").value = RowsAffected
        .Parameters("@AdditionalInfo").value = AdditionalInfo
        
        .Execute
    End With
    
    eventid = 0
    retcode = True
    GoTo PROC_EXIT
    
PROC_ERROR:
    Call ErrorHandler(err, conn)
    retcode = False
    Resume PROC_EXIT
    
    
PROC_EXIT:
    On Error Resume Next
    If conn.State <> ObjectStateEnum.adStateClosed Then conn.Close
    
End Function

Private Sub ErrorHandler(ByRef objErr As VBA.ErrObject, ByRef objConn As ADODB.Connection)
    'DO NOT USE "ON ERROR RESUME NEXT" in this routine.
    'THIS WILL CLEAR THE ERROR OBJECT!
    
    Select Case ethrowerrors
        Case eErrorHandlerMode.eIgnoreErrors:
            err.Clear
            
        Case eErrorHandlerMode.eRaiseAndThrow:
            If objConn.State <> ObjectStateEnum.adStateClosed Then objConn.Close
            MsgBox objErr.Description, vbExclamation, objErr.source
            err.Raise objErr.Number, objErr.source, objErr.Description
            
        Case eErrorHandlerMode.eRaiseOnly:
            If objConn.State <> ObjectStateEnum.adStateClosed Then objConn.Close
             MsgBox objErr.Description, vbExclamation, objErr.source
             err.Clear
           
        Case eErrorHandlerMode.eThrowOnly:
            If objConn.State <> ObjectStateEnum.adStateClosed Then objConn.Close
            err.Raise objErr.Number, objErr.source, objErr.Description
        
        Case Default:
        
    End Select
    
End Sub
Private Sub Class_Terminate()
    'Destructor. Executes when the object is destroyed.
    On Error Resume Next
    
    If conn.State <> ObjectStateEnum.adStateClosed Then conn.Close
    Set conn = Nothing
    Set cmd = Nothing
    
End Sub

Open in new window

0
Nick67Commented:
Ok,
from the look of it you need code to instantiate the class

Dim MyLogger as New clsISCenterLogger
Set MyLogger.eErrorHandlerMode = 0 'or whatever value between 0 and 3 is appropriate
'Then it looks like you may need to open the log and close it with passed in parameters
MyLogger.OpenLogRecord
MyLogger.CloseLogRecord(RowsAffected , ErrMsg , AdditionalInfo , StepSucceeded )
'and destroy it
Set MyLogger = nothing
0
Karen SchaeferBI ANALYSTAuthor Commented:
Nick,

Thanks for your great work,  I am getting a missing object variable.  could use another pair of eyes.

Here is what I have so far.

I am declaring the object in the following code - on startup of the app.
'========================================================================================
' Code change by 49kxs, 10/08/2013 to Handle Event Log Tracking
Public gEventLogger As clsISCenterLogger_EventLog
'========================================================================================

Public Function AppStartup(Optional UnattendedMode As Boolean = False)
    
    On Error GoTo PROC_ERROR
    Const ProcName = "AppStartup"
    
    gEventLogger.ThrowErrors
    'This function is called at startup by the AUTOEXEC macro.

'========================================================================================
' Code change by 49mwg, 25-July-2011
'
    gbUnattendedMode = UnattendedMode
'
' END OF CODE CHANGE 25-JULY-2011
'========================================================================================

    iMaxErrorEmails = 0
   
    Set oDebug = New cls_DebugLog
    Set gEventLogger = New clsISCenterLogger_EventLog
    
    DoCmd.OpenForm FormName:="frmJobs", _
                   View:=acNormal
                   
    GoTo PROC_EXIT
   
PROC_ERROR:
    Call HandleError(Err.Number, Err.Description, MODNAME & "::" & ProcName & "()")
    Resume PROC_EXIT

PROC_EXIT:
    On Error Resume Next

End Function

Open in new window


See any code related to gEventLogger
Public Sub InvoicesLoad(Optional ByVal JobList As Form_frmjobs = Nothing, _
                                    Optional ByVal Confirm As Boolean = True, _
                                    Optional ByVal RptName As String = "", _
                                    Optional ByVal rptDesc As String = "")

    Const ProcName = "InvoicesLoad"

    '  07/25/2008  49rsc   added logging
    '  02/12/2010  49rsc   Call InvoicesLoad_RunSQLAgentJob
    '  10/8/2013   49kxs   Added Event Logging
    Dim ReportName    As String
    Dim ReportDesc    As String

    On Error GoTo ErrorHandler
    
    gEventLogger.ThrowErrors = eIgnoreErrors

    If RptName <> "" Then
        ReportName = RptName
        ReportDesc = rptDesc
    Else
        ReportName = JobList.CurrentJob
        ReportDesc = JobList.CurrentJobDesc
    End If

    Dim dbs As Database
    Dim rs As Recordset
    Set dbs = CurrentDb
    Set rs = dbs.OpenRecordset("tblSelectionMenu")

    rs.FindFirst "RptName = '" & ReportName & "'"
    Dim Msg, Desc As String
    Msg = "Do you want to proceed with your selection: " & ReportName & "?"
    Desc = Msg & vbCrLf & vbCrLf & "REPORT/TOOL DESCRIPTION:" & vbCrLf & rs.Fields("Description")

    If Confirm Then
        If MsgBox(Desc, vbYesNo, "Update/Report Selection") = vbNo Then
            Exit Sub
        End If
    End If

    ' 3/31/05 49mam: Change SendKeys to Automation
    '---------------------------------------------
    Dim xla       As Excel.Application
    Dim xlb       As Excel.Workbook
    Dim xls       As Excel.Worksheet
    Dim sFiles    As String ' source filename
    Dim sFileD    As String ' destination filename
    Dim StartTime As Date
    Dim EndTime   As Date
    Dim RunTime   As String

    Const cstrImportTableName   As String = "tblInvoices_Import"
    Const cstrAccumulatorName   As String = "tblInvoicesData"
    Dim lngRC_Import        As Long
    Dim lngRC_BeforeAppend  As Long
    Dim lngRC_AfterAppend   As Long
    Dim sErr                As String

    StartTime = Time

    sFiles = csPathEDIInvoices & Format(date, "YYYYMMDD") & " EDIInvoices.xls"
    sFileD = csPathImportData & "InvoiceData.xls"

    
    ' delete destination file, if it exists
    On Error Resume Next
    Kill sFileD
    On Error GoTo ErrorHandler

    ' copy source file to destination file
    If Dir(sFiles) = "" Then
        Err.Raise Number:=clErrBoeingInvoicesNoData, _
                  Description:=Replace(csErrBoeingInvoicesNoData, "%srcfile%", sFiles)
    End If
    On Error Resume Next
    
    FileCopy sFiles, sFileD
    
        If Err Then
            sErr = csErrFileCopy
            sErr = Replace(sErr, "%source%", sFiles)
            sErr = Replace(sErr, "%dest%", sFileD)
            Err.Raise Number:=clErrFileCopy, _
                      Description:=sErr
       If gEventLogger.IsOpen = False Then gEventLogger.OpenLogRecord
        
        End If

    On Err GoTo ErrorHandler

    ' launch Excel and open destination file
    Set xla = New Excel.Application
    Set xlb = xla.Workbooks.Open(FileName:=sFileD)
    Set xls = xlb.Worksheets(1)

    ' format report
    '====================================================================
    ' CODE CHANGE 18-July-2012 by 49mwg

        FormatReport_ThrowErrors (xls)
    '====================================================================

    ' re-format column "BH" (format as Date and remove Time portion from cell values)
    With xls.Range("BH:BH")
        .NumberFormat = "mm/dd/yy"
        .Replace " *", ""
    End With

    ' save and close
    xlb.Save
    xlb.Close False
    xla.Quit
    '---------------------------------------------

    DoCmd.SetWarnings (False)
    DoCmd.OpenQuery ("qryCLEAR:Invoices_Import")

    If gEventLogger.IsOpen = False Then gEventLogger.OpenLogRecord
        'Imports S:\49bse\Invoices Sent To Boeing\Edi\InvoiceUpdate.xls
        DoCmd.TransferSpreadsheet acImportDelim, , "tblInvoices_Import", _
            csPathImportData & "InvoiceData.xls", True
    
        If gEventLogger.IsOpen = True Then _
            gEventLogger.CloseLogRecord RowsAffected:=lngRC_Import, _
                                  ErrMsg:=Err.Description, _
                                  AdditionalInfo:=MODNAME & " - " & ProcName, _
                                  StepSucceeded:=1

   On Err GoTo ErrorHandler

    mWriteLog_RecordCounts cstrImportTableName, "Records imported", lngRC_Import
    mWriteLog_RecordCounts cstrAccumulatorName, "Accumulator records before append", lngRC_BeforeAppend

    'Appends imported data into the database
    DoCmd.OpenQuery ("qryAPPEND:InvoiceData")
    
    If gEventLogger.IsOpen = False Then gEventLogger.OpenLogRecord
        If gEventLogger.IsOpen = True Then _
           gEventLogger.CloseLogRecord RowsAffected:=lngRC_AfterAppend, _
                        ErrMsg:=Err.Description, _
                        AdditionalInfo:=MODNAME & " - " & ProcName, _
                        StepSucceeded:=1
    On Err GoTo ErrorHandler

    mWriteLog_RecordCounts cstrAccumulatorName, "Accumulator records after append", lngRC_AfterAppend

    ''  02/12/2010
    Call InvoicesLoad_SQLJob_InvoiceData2

    DoCmd.SetWarnings (True)

    'Logs update time in tblUpdateLog
    Dim rs1, rs2, rs3 As Recordset
    Dim ct As String
    
    Set rs1 = dbs.OpenRecordset("tblUpdateLog")
        rs1.Edit
        rs1.Fields("Invoices") = date
        rs1.Update
    
    Set rs2 = dbs.OpenRecordset("tblInvoices_Import")
        rs2.MoveLast
    
    ct = Format(rs2.RecordCount, "###,###")
    
    EndTime = Time
    RunTime = (EndTime - StartTime)
    RunTime = Format(RunTime, "hh:mm:ss")

    Set rs3 = dbs.OpenRecordset("tblDailyLog")
        rs3.FindFirst "Name = '" & ReportName & "'"
        rs3.Edit
        rs3.Fields("UpdateDate") = date
        rs3.Fields("RunTime") = RunTime
        rs3.Update

    If Confirm Then SafeMsgBox "Update complete." & vbCrLf & vbCrLf & ct & _
        " records imported." & vbCrLf & "Run Time: " & RunTime, , _
        "Database Information"

ExitHandler:
    On Error Resume Next

    xlb.Close False
    xla.Quit

    Set xls = Nothing
    Set xlb = Nothing
    Set xla = Nothing

    Exit Sub

ErrorHandler:
    DoCmd.Hourglass (False)
    DoCmd.SetWarnings (True)

    sErr = "InvoicesLoad() -- " & Err.Number & ". " & Err.Description

    Call HandleError(ErrNumber:=Err.Number, _
                                    ErrDescription:=Err.Description, _
                                    ErrTitle:=Err.source, _
                                    module:=MODNAME, _
                                    Procedure:=ProcName)
    'Add to Error handling if error is risen use to closelogRecord
    gEventLogger.CloseLogRecord RowsAffected:=lngRC_Import, _
                           ErrMsg:=Err.Number & ". " & Err.Description, _
                           AdditionalInfo:=MODNAME & " - " & ProcName, _
                           StepSucceeded:=1

    mWriteLogIndent 2, sErr

    Resume ExitHandler
End Sub

Open in new window

0
Nick67Commented:
Start here.
You do have Option Explicit at the beginning of each module right?

I am not seeing a Dim statement for
Set oDebug = New cls_DebugLog
Where is oDebug declared?

On what line of code does it break?
0
Nick67Commented:
If it isn't giving you problems then

    If gEventLogger.IsOpen = False Then gEventLogger.OpenLogRecord
        If gEventLogger.IsOpen = True Then _
           gEventLogger.CloseLogRecord RowsAffected:=lngRC_AfterAppend, _
                        ErrMsg:=Err.Description, _
                        AdditionalInfo:=MODNAME & " - " & ProcName, _
                        StepSucceeded:=1

May be ok, but I am not sure that I follow the logic -- or that it is right, or that it may not run into grief

    If gEventLogger.IsOpen = False Then gEventLogger.OpenLogRecord

Is fine -- just so long as it happens really, really fast.  If not something like

Dim Wait As Double
Dim Issued as Boolean
issued =  False
Wait = Timer + 1 (or however main seconds it may take
'now these structures pause things up until the log is open
Do While gEventLogger.IsOpen = False And Timer < Wait
    if issued = false then
        gEventLogger.OpenLogRecord
        Issued = true
    end if
    If gEventLogger.IsOpen = True Then
        Exit Do
    End If
    DoEvents 'wait for the log to open
Loop

Open in new window


But I don't know that the logic is good either.  You've got it opening the log if it isn't open.  There's no check to see if that succeeded; it moves on.  Then you have it punching in the log details, and also no check for success.

It may all work well -- but it isn't robust from an unexpected error point of view.  You've got gEventLogger as a public variable.  But those are notorious for ceasing to exist after an error -- and you don't have code to confirm that it does in fact exist before issuing OpenLogRecord or CloseLogRecord.  

And what works well in coding and testing may not hold up under end-user use.
0
Karen SchaeferBI ANALYSTAuthor Commented:
Sorry I am unable to get it to work,  I was hoping to take it step by step.  I am confused on how to incorporate the code into my sample test code.  I verified my test code runs without the EventLog code, so now I want to start to incorporate the Event log code.


"But I don't know that the logic is good either.  You've got it opening the log if it isn't open.  There's no check to see if that succeeded; it moves on.  Then you have it punching in the log details, and also no check for success."  
       
               See the StepSucceeded portion of the CloseLogRecord

So Step1 should be What?

I created the global declaration of the class.

Public gEventLogger As clsISCenterLogger_EventLog

In the AppStatup I included the following.

     gEventLogger.ThrowErrors = eIgnoreErrors

Where in the code should I set the

    If gEventLogger.ISOpen = False the gEventLogger.OpenLogRecord.

Note::  My current placement does not error, but also does not write a record to the SQL table.  SEE Line 120

Public Sub InvoicesLoad(Optional ByVal JobList As Form_frmjobs = Nothing, _
                                    Optional ByVal Confirm As Boolean = True, _
                                    Optional ByVal RptName As String = "", _
                                    Optional ByVal rptDesc As String = "")
    ' 3/31/05 49mam: Change SendKeys to Automation
    '---------------------------------------------
    Dim xla                     As Excel.Application
    Dim xlb                     As Excel.Workbook
    Dim xls                     As Excel.Worksheet
    Dim sFiles                  As String ' source filename
    Dim sFileD                  As String ' destination filename
    Dim StartTime               As Date
    Dim EndTime                 As Date
    Dim RunTime                 As String
    Dim lngRC_Import            As Long
    Dim lngRC_BeforeAppend      As Long
    Dim lngRC_AfterAppend       As Long
    Dim sErr                    As String
    '  07/25/2008  49rsc   added logging
    '  02/12/2010  49rsc   Call InvoicesLoad_RunSQLAgentJob
    '  10/8/2013   49kxs   Added Event Logging
    Dim ReportName              As String
    Dim ReportDesc              As String
    Dim dbs                     As Database
    Dim rs, rs1, rs2, rs3       As Recordset
    Dim ct                      As String
    
    Const ProcName = "InvoicesLoad"
    Const cstrImportTableName   As String = "tblInvoices_Import"
    Const cstrAccumulatorName   As String = "tblInvoicesData"

    On Error GoTo ErrorHandler
    
    Set dbs = CurrentDb
    
    If RptName <> "" Then
        ReportName = RptName
        ReportDesc = rptDesc
    Else
        ReportName = JobList.CurrentJob
        ReportDesc = JobList.CurrentJobDesc
Debug.Print ReportName & " - " & ReportDesc
    End If

'    Set rs = dbs.OpenRecordset("tblSelectionMenu")
'
'    'rs.FindFirst "RptName = '" & ReportName & "'"
'    Dim Msg, Desc As String
'    Msg = "Do you want to proceed with your selection: " & ReportName & "?"
'    Desc = Msg & vbCrLf & vbCrLf & "REPORT/TOOL DESCRIPTION:" & vbCrLf & ReportDesc 'rs.Fields("Description")
'
'    If Confirm Then
'        If MsgBox(Desc, vbYesNo, "Update/Report Selection") = vbNo Then
'            Exit Sub
'        End If
'    End If


    StartTime = Time

    sFiles = csPathEDIInvoices & Format(date, "YYYYMMDD") & " EDIInvoices.xls"
    sFileD = csPathImportData & "InvoiceData.xls"

    ' delete destination file, if it exists
    On Error Resume Next
        Kill sFileD
    On Error GoTo ErrorHandler

    ' copy source file to destination file
    If Dir(sFiles) = "" Then
        Err.Raise Number:=clErrBoeingInvoicesNoData, _
                  Description:=Replace(csErrBoeingInvoicesNoData, "%srcfile%", sFiles)
    End If
    
    On Error Resume Next
    
    FileCopy sFiles, sFileD
    
        If Err Then
            sErr = csErrFileCopy
            sErr = Replace(sErr, "%source%", sFiles)
            sErr = Replace(sErr, "%dest%", sFileD)
            Err.Raise Number:=clErrFileCopy, _
                      Description:=sErr
        
            If gEventLogger.IsOpen = False Then gEventLogger.OpenLogRecord
        
        End If

    On Err GoTo ErrorHandler

    ' launch Excel and open destination file
    Set xla = New Excel.Application
    Set xlb = xla.Workbooks.Open(FileName:=sFileD)
    Set xls = xlb.Worksheets(1)

    ' format report
    '====================================================================
    ' CODE CHANGE 18-July-2012 by 49mwg
        FormatReport_ThrowErrors (xls)
    '====================================================================
    ' re-format column "BH" (format as Date and remove Time portion from cell values)
    With xls.Range("BH:BH")
        .NumberFormat = "mm/dd/yy"
        .Replace " *", ""
    End With

    ' save and close
    xlb.Save
    xlb.Close False
    xla.Quit
    '====================================================================

    DoCmd.SetWarnings (False)
    
    mWriteLog_RecordCounts cstrImportTableName, "Records imported", lngRC_Import
    
    DoCmd.OpenQuery ("qryCLEAR:Invoices_Import")
      '====================================================================
    If gEventLogger.IsOpen = False Then gEventLogger.OpenLogRecord
        'Imports S:\49bse\Invoices Sent To Boeing\Edi\InvoiceUpdate.xls
        DoCmd.TransferSpreadsheet acImportDelim, , "tblInvoices_Import", _
            csPathImportData & "InvoiceData.xls", True

        If gEventLogger.IsOpen = True Then _
            gEventLogger.CloseLogRecord RowsAffected:=lngRC_Import, _
                                  ErrMsg:=Err.Description, _
                                  AdditionalInfo:=MODNAME & " - " & ProcName, _
                                  StepSucceeded:=1
    On Err GoTo ErrorHandler

    'Appends imported data into the database
    DoCmd.OpenQuery ("qryAPPEND:InvoiceData")
      '====================================================================

'    If gEventLogger.IsOpen = False Then gEventLogger.OpenLogRecord
'        If gEventLogger.IsOpen = True Then _
'           gEventLogger.CloseLogRecord RowsAffected:=lngRC_AfterAppend, _
'                        ErrMsg:=Err.Description, _
'                        AdditionalInfo:=MODNAME & " - " & ProcName, _
'                        StepSucceeded:=1
    On Err GoTo ErrorHandler

    mWriteLog_RecordCounts cstrAccumulatorName, _
        "Accumulator records after append", lngRC_AfterAppend

    ''  02/12/2010
'    Call InvoicesLoad_SQLJob_InvoiceData2

    DoCmd.SetWarnings (True)

    'Logs update time in tblUpdateLog
    Set rs1 = dbs.OpenRecordset("tblUpdateLog")
        rs1.Edit
        rs1.Fields("Invoices") = date
        rs1.Update
    
    Set rs2 = dbs.OpenRecordset("tblInvoices_Import")
        rs2.MoveLast
    
    ct = Format(rs2.RecordCount, "###,###")
    
    EndTime = Time
    RunTime = (EndTime - StartTime)
    RunTime = Format(RunTime, "hh:mm:ss")

    Set rs3 = dbs.OpenRecordset("tblDailyLog")
        rs3.FindFirst "Name = '" & ReportName & "'"
        rs3.Edit
        rs3.Fields("UpdateDate") = date
        rs3.Fields("RunTime") = RunTime
        rs3.Update

    If Confirm Then SafeMsgBox "Update complete." & vbCrLf & vbCrLf & ct & _
        " records imported." & vbCrLf & "Run Time: " & RunTime, , _
        "Database Information"

ExitHandler:
    On Error Resume Next

    xlb.Close False
    xla.Quit

    Set xls = Nothing
    Set xlb = Nothing
    Set xla = Nothing

    Exit Sub

ErrorHandler:
    DoCmd.Hourglass (False)
    DoCmd.SetWarnings (True)

   ' sErr = "InvoicesLoad() -- " & Err.Number & ". " & Err.Description

    Call HandleError(ErrNumber:=Err.Number, _
                                    ErrDescription:=Err.Description, _
                                    ErrTitle:=Err.source, _
                                    module:=MODNAME, _
                                    Procedure:=ProcName)
    
    'Add to Error handling if error is risen use to closelogRecord
   ' gEventLogger.CloseLogRecord RowsAffected:=lngRC_Import, _
                           ErrMsg:=Err.Number & ". " & Err.Description, _
                           AdditionalInfo:=MODNAME & " - " & ProcName, _
                           StepSucceeded:=1

    mWriteLogIndent 2, sErr

    Resume ExitHandler
End Sub

Open in new window


Thanks again for your time and input.

K
0
Karen SchaeferBI ANALYSTAuthor Commented:
Ok further testing/debuging.  shows the " gEventLogger" as object variable or with variable not set.  What am I missing.

I tried setting as Public and as Global - neither solved the issue.

Kerr
0
Nick67Commented:
I am not really seeing what I think you want in the latest code block
Where is "Public gEventLogger As clsISCenterLogger_EventLog"
Did I miss it?
And since it is a class -- and we want to use it in InvoicesLoad() -- you should have


Dim gEventLogger As  New clsISCenterLogger_EventLog

Throw that in at line 27, see what results
0
Karen SchaeferBI ANALYSTAuthor Commented:
ok that got the first part of the record into the SQL Table, now where should i put the second half of the code that inputs the record count and other info - ie. closelogRecord portion of the code.

Qry
0
Karen SchaeferBI ANALYSTAuthor Commented:
Ok further debugging shows that it is not capturing the current Function, but the OpenLogRecord and passing that as the parameter value.

Public Function OpenLogRecord() As Boolean
    Const ProcName = "OpenLogRecord"
    On Error GoTo PROC_ERROR
    
    Dim retcode As Boolean
    
    If eventid > 0 Then Err.Raise 10001, MODNAME & "::" & ProcName, _
        "Method OpenLogRecord() may not be called when a log record is already open."
    retcode = True
    conn.Open
    
    With cmd
        .ActiveConnection = conn
        .CommandType = adCmdStoredProc
        .CommandText = cmdstr
        .NamedParameters = True
    
        'Dim pEventLog As ADODB.Parameter
        'For Each pEventLog In cmd.Parameters
        '    Debug.Print pEventLog.Name
        'Next pEventLog
        
   [subtitle]     .Parameters("@EventName").value = "EventLog Event"
        .Parameters("@ModuleName").value = MODNAME '"EventLog Module"
        .Parameters("@ProcedureName").value = ProcName '"EventLog Procedure"
[/subtitle]        
        .Execute
    End With
    
    eventid = cmd.Parameters("@RETURN_VALUE").value
    retcode = True
    GoTo PROC_EXIT
    
PROC_ERROR:
    Call ErrorHandler(Err, conn)
    retcode = False
    Resume PROC_EXIT
    
PROC_EXIT:
    On Error Resume Next
    If conn.State <> ObjectStateEnum.adStateClosed Then conn.Close
    
End Function

Open in new window


What am I missing?

K
0
Nick67Commented:
I didn't build the class, you did :)

Do you understand how classes work? :)
0
Karen SchaeferBI ANALYSTAuthor Commented:
Not all together - I inheredit the code - and trying to understand it and get it to work.

Thanks for all you time and input -

Quiting time - pick this up on Monday.

Have a great weekend.

Karen
0
Nick67Commented:
A class has properties
The Get and Let functions in the class are the respective ways that you read (Get) and write(Let) the various properties

A class also has methods, things that it does.  Yours has OpenLogRecord and CloseLogRecord.
(Some every class has that deal with housekeeping -- initialize and terminate--that you don't call.

Now, by rights, when your invoice sub calls
            gEventLogger.CloseLogRecord RowsAffected:=lngRC_Import, _
                                  ErrMsg:=Err.Description, _
                                  AdditionalInfo:=MODNAME & " - " & ProcName, _
                                  StepSucceeded:=1


It should pass some values into the class, and execute the class's CloseLogRecord sub.

Throw some breakpoints in that class.  Are the correct values being passed in?

Moreover, just where do lngRC_Import, MODNAME, ProcName, and 1 (which is what you are passing in) get set, changed or initialized?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks for the input and time - I am no longer working this problem - left position.

K
0
Nick67Commented:
Ah,

Well, wherever you go from there, I wish you the best of success.
I've help you with a few of your problems and it is clear you have a solid skillset.
You'll be a valuable asset to whatever organization employs your skills next.

Good Luck!
Nick67
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.