Hi,



This code has been driving me crazy!!!!  It's the on error statement.  For

some reason 'errnotify' code is run even if there is no error.  Can anyone

see anything wrong?



Dim strResponse As String

Dim strPoint As String



On Error GoTo errnotify



strResponse = InputBox("Please specify a file name", "Excel Export")

strPoint = InStr(strResponse, ".")



If Len(strResponse) > 0 Then

    If strPoint > 0 Then

        strResponse = Left(strResponse, (strPoint - 1))

    End If

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,

"qryBuildingBlock", "C:\" & strResponse & ".xls"

End If



errnotify:

    errorHandle "strLocation", "strSource", "strNumber", "strDescription"

    Exit Sub



Many thanx,



Mark

Mark,



Place an Exit Sub ahead of the error label



Walt

There's nothing wrong, that's how labels work.  A label does not change the

flow of program execution, so control just flows right into your error

block.  Add an 'Exit Sub' just before the label and you should see the

behavior you expect.



David Carnley





 -----Original Message-----

From: 	Mark Irvine [mailto:mark@m...] 

Sent:	Monday, January 28, 2002 9:17 AM

To:	Access

Subject:	[access] On Error Problem



Hi,



This code has been driving me crazy!!!!  It's the on error statement.  For

some reason 'errnotify' code is run even if there is no error.  Can anyone

see anything wrong?



Dim strResponse As String

Dim strPoint As String



On Error GoTo errnotify



strResponse = InputBox("Please specify a file name", "Excel Export")

strPoint = InStr(strResponse, ".")



If Len(strResponse) > 0 Then

    If strPoint > 0 Then

        strResponse = Left(strResponse, (strPoint - 1))

    End If

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,

"qryBuildingBlock", "C:\" & strResponse & ".xls"

End If



errnotify:

    errorHandle "strLocation", "strSource", "strNumber", "strDescription"

    Exit Sub



Many thanx,



Mark

Walt,



Thanks!!!!  Been one of those days.



Mark



"Walt Morgan" <wmorgan@s...> wrote in message

news:140219@a...

>

> Mark,

>

> Place an Exit Sub ahead of the error label

>

> Walt

>

>

>

>

Mark,



InStr returns a Long NOT a String. Thus you are getting a Type Mismatch in

strPoint = Instr(.....



Ian Ashton





-----Original Message-----

From: Mark Irvine [mailto:mark@m...]

Sent: Monday, January 28, 2002 3:17 PM

To: Access

Subject: [access] On Error Problem





Hi,



This code has been driving me crazy!!!!  It's the on error statement.  For

some reason 'errnotify' code is run even if there is no error.  Can anyone

see anything wrong?



Dim strResponse As String

Dim strPoint As String



On Error GoTo errnotify



strResponse = InputBox("Please specify a file name", "Excel Export")

strPoint = InStr(strResponse, ".")



If Len(strResponse) > 0 Then

    If strPoint > 0 Then

        strResponse = Left(strResponse, (strPoint - 1))

    End If

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,

"qryBuildingBlock", "C:\" & strResponse & ".xls"

End If



errnotify:

    errorHandle "strLocation", "strSource", "strNumber", "strDescription"

    Exit Sub



Many thanx,



Mark

The procedure works through the code to be run if no error and then falls

through into the error routine - just put Exit Sub before the error routine

label.



Joe





Dim strResponse As String

Dim strPoint As String



On Error GoTo errnotify



strResponse = InputBox("Please specify a file name", "Excel Export")

strPoint = InStr(strResponse, ".")



If Len(strResponse) > 0 Then

    If strPoint > 0 Then

        strResponse = Left(strResponse, (strPoint - 1))

    End If

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,

"qryBuildingBlock", "C:\" & strResponse & ".xls"

End If



Exit Sub



errnotify:

    errorHandle "strLocation", "strSource", "strNumber", "strDescription"

    Exit Sub





*************************************************************************



This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.



The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:

Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions

CIS Unit Managers Limited Registered in England and Wales number 2369965  - for unit trusts and PEPs

CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name

Registered offices: Miller Street, Manchester M60 0AL   Telephone  0161-832-8686   Internet  http://www.cis.co.uk   E-mail
cis@c...



CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.



CIS is a member of the General Insurance Standards Council



CIS & the CIS logo (R) Co-operative Insurance Society Limited



********************************************************************************