Error Handler Always Runs - Even with no Error

[WaterGeek]

I have learned a lot from reading these forums, but this is my first post...

I have a form with button to export different queries to an excel file. The query exports fine but my error handler runs every time. I have other forms with the same error handler format and the problem does not happen in those, so I do not think is is an IDE setting for error handling.

The code runs fine with no error handler.

After executing code I get one error message with Err.Number = 0 and Err.Description = ""

Then a second error message where Err.Number = 20 and Err.Description = "Resume without error"

I had experimented with a creating code to prevent an endless loop of error messages. I commented that code out to simplify and troubleshoot but that did not solve it. When I loaded the code with break points I did not get any error messages!

Here is the code:

Option Compare Database
Option Explicit

Private Sub AllContactsButton_Click()
Dim QueryName As String
Dim ExcelFileName As String
Dim UserName As String
Dim ExportMonth As String
Dim ExportDay As String
Dim ExportYear As String
Dim ExportDate As String
Dim ErrorCount As Integer
Dim ExcelApp As Object

'Create an error pop-up
'ErrorCount = 0
On Error GoTo Err_Msg:

'Put Export Date in a format that can be used in a file name (no "\")
ExportMonth = Month(Date)
ExportDay = Day(Date)
ExportYear = Year(Date)
ExportDate = ExportMonth & "_" & ExportDay & "_" & ExportYear

'***To Edit Code to export different types of contacts, Only Edit Query name and file name in this block***
'set user name, path to desktop and query name
UserName = Environ("USERNAME")
ExcelFileName = "c:\Documents and Settings\" & UserName & "\Desktop\SDWIS_All_Contacts_" & ExportDate & ".xls"
QueryName = "WaterSystemContacts-All"

'Export Query Results
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, QueryName, ExcelFileName
If MsgBox("All contacts were exported to your desktop. Do you want to open the file?", vbYesNo) = vbYes Then
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = True
ExcelApp.Workbooks.Open FileName:=ExcelFileName
DoCmd.Close
Else
DoCmd.Close
End If



Err_Msg:
'ErrorCount = ErrorCount + 1
'If ErrorCount < 5 Then
MsgBox " Error #" & Err.Number & ": " & Err.Description
Resume Next
'Else
'MsgBox Err.Description & "You have had " & ErrorCount & " errors. Contact your SDWIS administrator."
'GoTo Err_Exit
'End If


Err_Exit:
Exit Sub


End Sub

 

[RoyVidar]

Switch the errorhandler and the exit handle.

Error 0 means no error, then, when trying to do a resume without there being any error, an error occurs.

[tt]...
Err_Exit:
Exit Sub
Err_Msg:
MsgBox " Error #" & Err.Number & ": " & Err.Description
Resume Err_Exit
End Sub [/tt]

Roy-Vidar

 

[WaterGeek]

Wow, that was a simple fix...but I pobably would never have found it!


Thank you