Count # of Errors Occuring in a function

[Guest]

All, I have a function to import in a series of files daily. There is a sql
statement after each import to insert a message into message table that it
worked. What I am hoping however is that I can count how many times (if
needed) that an occurred so I can also insert a message the import was a
successful. The general code is

Function ImportProc()
On Error Resume Nex
''============================================================================''
'' Import Procedure
''
'' This procedure looks for the presence of the most recent completed
batch's ''
'' file which is inferred by Today - 1 day = compensation date. If it is not
''
'' present it will log a message. On Thursdays it will look for reprocessing
''
'' results also. Once the Import it calls "Import Transfer"
'
''============================================================================''

'' If it is monday don't run because nothing will be there ''
If Weekday(Date) = 1 Then ' 1 = Monday'
Exit Function
Else
End If

'' Identify all the File Paths ''
Dim AdvCase As String
Dim CompSummary As String
Dim DupTrans As String
Dim NoCredits As String
Dim SystemErrors As String
Dim AdvCasefile As String
Dim CompSummaryfile As String
Dim DupTransfile As String
Dim NoCreditsfile As String
Dim SystemErrorsfile As String
Dim Yesterday As String
Dim Fileext As String
Dim ServerPath As String

ServerPath = "\\a0001-app0102-s\xcomcmd\"
AdvCase = "Advance Case"
CompSummary = "Comp Summary"
DupTrans = "Dup Transactions"
NoCredits = "No Credits"
SystemErrors = "System Errors"

Yesterday = Format(Date - 1, "MM-DD-YY")
Fileext = ".txt"
Dim insertp1 As String
Dim insertp2 As String
Dim insertp3 As String

AdvCasefile = ServerPath & "Advance Case Sample.txt" ''& Yesterday & Fileext
CompSummaryfile = ServerPath & "comp summary sample.txt" ''& Yesterday &
Fileext''
DupTransfile = ServerPath & "Duplicate Transactions Sample.txt" '' &
Yesterday & Fileext
SystemErrorsfile = ServerPath & "System errors sample.txt" ''& Yesterday &
Fileext
NoCreditsfile = ServerPath & Yesterday & Fileext


insertp1 = "INSERT INTO Messages (messagetype,messageerror) VALUES ("
insertp2 = """The "
insertp3 = " file wasn't there, Import failed" & """" & ");"



''Start Looking for files to import
DoCmd.SetWarnings False

'--Advance case--'
If Len(Dir(AdvCasefile)) > 0 Then
DoCmd.TransferText acImportDelim, "Advance Case Import Specification",
"Import_Advance_Case", AdvCasefile, True
Else
DoCmd.RunSQL insertp1 & "'" & AdvCase & "'," & insertp2 & AdvCase & " " &
Yesterday & insertp3
End If

''--System Errors--''
If Len(Dir(SystemErrorsfile)) > 0 Then
DoCmd.TransferText acImportDelim, "System Errors Import Specification",
"Import_System_Errors", SystemErrorsfile, True
Else
DoCmd.RunSQL insertp1 & "'" & SystemErrors & "'," & insertp2 & SystemErrors
& " " & Yesterday & insertp3
End If

''--CompSummary--'
If Len(Dir(CompSummaryfile)) > 0 Then
DoCmd.TransferText acImportDelim, "Comp Summary Import Specification",
"Import_Comp_Summary", CompSummaryfile, True
Else
DoCmd.RunSQL insertp1 & "'" & CompSummary & "'," & insertp2 & CompSummary &
" " & Yesterday & insertp3
End If

''--DupTrans---''
If Len(Dir(DupTransfile)) > 0 Then
DoCmd.TransferText acImportDelim, "Duplicate Transactions Import
Specification", "Import_Duplicate_Transactions", DupTransfile, True
Else
DoCmd.RunSQL insertp1 & "'" & DupTrans & "'," & insertp2 & DupTrans & " " &
Yesterday & insertp3
End If

Call ImportTransfer

'' If it is Thursday Pick up reprocessing files too ''
If Weekday(Date) = 4 Then ' 4 = Thursday'
Call ReprocProcedure
Else
End If


Call RunCleanUp

End Function

 

[Alex Dybenko]

Hi,
perhaps you can use DCount():

DCount("*", "Messages") will return a number of records in Messages table

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

Thanks, I was so busy trying to figure out some built in code I had
overlooked this approach.