Derek Dowle


I have a routine that exports selected data to an excel spreadsheet that works perfectly. If an excel file with the name chosen already exists then the user is warned.

If the data is being exported to a file that is open then a program error message appears and when help is pressed 'File access is denied (Error 1705)', which is to be expected.

To overcome this I have used the following code to intercept the error and to provide the user with a message advising them what to do.

ON ERROR DO errHandler

** export the data

SET SAFETY OFF

COPY TO (gcXlsFile) XLS && Create Excel file

SET SAFETY ON

ON ERROR && Restores system error handler.

PROCEDURE errHandler

PARAMETER merror

CLEAR

MESSAGEBOX("Message text" ,16 , "Message Title" )

ENDPROC

This code traps the error. However, after the PROCEDURE errHandler is run the behaviour of the form changes, as some controls beome invisible when the mouse pointer is passed over them and only become visible again when the mouse pointer hovers over the control.

CommandButtons, OptionButtons, Checkboxes, ComboBoxes and PageFrame page tabs are affected.

If a control's enabled property = .F. when the procedure is run it is not affected.

On the form there is a commandbutton that opens another form. When this other form is closed and focus returns to the first form the affected controls remain visible. This is also the case if the focus moves to another page of the PageFrame and then returns to the original page.

Can any one please explain what is happening and also how to overcome the problem.




Re: ON ERROR causes controls on forms to disappear

MarciaAkins


Derek Dowle wrote:

This code traps the error. However, after the PROCEDURE errHandler is run the behaviour of the form changes, as some controls beome invisible when the mouse pointer is passed over them and only become visible again when the mouse pointer hovers over the control.

Can any one please explain what is happening and also how to overcome the problem.

What happens if you set the form's Themes property to .F.







Re: ON ERROR causes controls on forms to disappear

Naomi Nosonovsky

What version of VFP you're using In VFP9 (VFP8) I would rather use TRY/CATCH block for this purpose. In your code why do you need CLEAR I believe CLEAR may cause problems with the form.




Re: ON ERROR causes controls on forms to disappear

fvp4ever

Yes, I've seen such problem too,
but this is not result of On Error
I've solve this problem in two ways
1) create a new form copy all objects from old one (don't use save as...)
2) put this code in form.activate event
this.width=this.width+1
this.width=this.width-1

hope this will help you




Re: ON ERROR causes controls on forms to disappear

Derek Dowle

Marcia

Thank you for the assistance.

Setting THISFORM.Themes =.F. does solve the problem but in doing so the user loses the benefit of 'Themes'. To overcome this I set themes to .F. before the ON ERROR code then to .T. afterwards.





Re: ON ERROR causes controls on forms to disappear

Derek Dowle

Naomi

Thank you for the assistance

The application was first created in vfp3, migrated to vfp6 and recently migrated to vfp9 after which the users asked for the export of data facility to be added.

As you suggested I have removed CLEAR from the code and the problem has gone away. The reason that clear was used in the code was because I was following an example for ON ERROR from vfp help.

I have also changed from ON ERROR to the TRY/CATCH block with which I am more familiar from using Visual Basic. The problem does not manifest it self when using TRY/CATCH.

However when I move from the development environment and build the application and create an .exe for distribution other things happen when the application is run from the executable file.

Firstly, I am using 'COPY TO (filename) XLS' to create the Excel spreadsheet. When this code is run the SaveAs dialog box appears. If the 'cancel' commandbutton is selected the dialog box closes and so does the application. How can I stop the application from closing

Secondly, when the TRY/CATCH block identifies that a file with the same name is open another problem occurs. The export code is initiated by a commandbutton. If any other control is selected, after the export button is used and the error trapped, which has code in its 'click' event then the code scrolls down the form, the code carries out its instructions and returns to the form which displays the controls in the foreground and the code remains in the background. How can I prevent the code from being displayed and scrolling down the screen.

Any help will be most welcome.





Re: ON ERROR causes controls on forms to disappear

Naomi Nosonovsky

Firstly, I am using 'COPY TO (filename) XLS' to create the Excel spreadsheet. When this code is run the SaveAs dialog box appears. If the 'cancel' commandbutton is selected the dialog box closes and so does the application. How can I stop the application from closing

I believe SET SAFETY is responsible for this dialog, which you originally had in your code. Make sure, it's still there.

I'm not sure how exactly you added try / catch, but if may be like this

Code Snippet

llContinue = .t.

try

copy to (filename) type xl5 && AFAIK it's newer format

catch to loException

** we can not proceed - file is in use (you may want to analyze exception)

llContinue = .f.

endtry

if m.llContinue

** Proceed with the extra command button logic

endif

That's a quick idea, if this would not solve your problem, I'll answer in more details later today.





Re: ON ERROR causes controls on forms to disappear

Derek Dowle

Naomi

The commandbutton calling the routine has very little code in it's click event. It basically gathers some boolean variable values to inform the program it is calling, ie.

THISFORM.SetUpPrinting

m.lShowSummary = .F.

m.lAllEntries = .T.

m.lPreview = .F.

m.lexport = .T.

DO programs\sortfirst

The program sorts data into a table to be either previeed, printed or exported.

The try\catch is virtually at the of the program only followed by a nesting ENDIF, ie

TRY

** export the data

SET SAFETY OFF

COPY TO (gcXlsFile) XLS && Create Excel file

SET SAFETY ON

CATCH

MESSAGEBOX("Message Text",16,"Message Title")

ENDTRY

As you can see the SET SAFETY is still there. If Safety is set to ON then both the dialog box and vfp ask the question of whether you want to overwrite the existing file. In any event the problem does not go away. Whether safety is on or off pressing the cancel commandbutton causes 'DO CANCEL' to appear in a wait window. before closing the dialog box and then the application.

I have looked at your code snippet and can understand the purpose of the 'llContinue' variable. But am unclear as to what the 'Proceed with the extra command button logic' might be. Also I am unclear as to how 'loException' is to be used. Please excuse my ignorance but I am not very experienced at error trapping in vfp.

Thank you for what you are doing.





Re: ON ERROR causes controls on forms to disappear

Naomi Nosonovsky

Hi Derek,

Answering your last question about loException you can get more info about the error this way:

Code Snippet

FUNCTION Log_Error
LPARAMETERS toError
LOCAL lcError, lcVars
*{ Commented at 22:24:47 on August 19, 2006
*!* LineContents is only useful within a program, does not work in run-time
*} End Commenting 22:24:47 - August 19, 2006

lcError = [Error: ] + TRANSFORM(m.toError.errorno) + CRLF + ;
[LineNo: ] + TRANSFORM(m.toError.LINENO) + CRLF + ;
[Message: ] + m.toError.MESSAGE + CRLF + ;
[Procedure: ] + m.toError.PROCEDURE + CRLF + ;
[Details: ] + m.toError.details + CRLF + ;
[StackLevel: ] + TRANSFORM(m.toError.stacklevel) + ;
IIF(_VFP.STARTMODE = 0, CRLF + [LineContents: ] + m.toError.linecontents, '')
RETURN m.lcError

I'm not sure about Save dialog and where it's coming from. Did you try to trace your code

Once you get the error, you need to exit gracefully and don't execute the rest of the code. So, if this code is in prg, you may return llContinue (in this case it would be false). In the code that called this prg instead of calling it with DO statement, you'll call it as a function

llProceed = MyFunc(arg)

if not llProceed

* we need to stop here - have an error in the prev. routine

endif

In other words, since you have an error in one procedure, you need to somehow pass the status up in the calling chain.





Re: ON ERROR causes controls on forms to disappear

Derek Dowle

Hi Naomi

I seem to be having problems using your code snippet to identify all the details of the Error. I call the Function in the CATCH block and then get error message 1924 saying toError is not an object. As an alternative I have used a Procedure from vfp Help that confirms the code line:

'COPY TO (filename) XL5'

is the cause of the problem, namely the program is attempting to overwrite an existing open file.

The changes that have been made to the code to date ensure that all works according to plan when working in the development environment.

The new problems only manifest themselves after the application has been built into and run from an executable file.

The first problems is; if the user changes their mind and closes the SaveAs dialog box by selecting Cancel, then this action not only closes the dialog box but the application as well and a Wait Window with DO CANCEL appears momentarily. How can I prevent the application from closing At this stage an error would not have occurred as no attempt had been made to save the Excel file.

The second problem is, if the user attempts to copy over an existing open file the error is trapped and a message telling the user what has happened and what to do appears. The application returns to the form. It is only when the user selects another control that the code scrolls down the screen reminiscent of when ignore is pressed when a system error message appears on the screen. This behaviour must be caused by the initial error that was trapped in the TRY block. How can this problem be overcome

Lastly is there a general reason why code functions correctly in the development environment and not in runtime





Re: ON ERROR causes controls on forms to disappear

Naomi Nosonovsky

Derek Dowle wrote:

I seem to be having problems using your code snippet to identify all the details of the Error. I call the Function in the CATCH block and then get error message 1924 saying toError is not an object. As an alternative I have used a Procedure from vfp Help that confirms the code line:

'COPY TO (filename) XL5'

is the cause of the problem, namely the program is attempting to overwrite an existing open file.

I'm sorry I haven't yesterday showed you how do I call this function.

Here's how I usually do:

Code Snippet

try

copy to (xlsFile) type xl5 && would not copy memo fields and would copy only 64K of records

catch to loException

lcErrorMsg = Log_Error(loException)

=messagebox(lcErrorMsg, 0, 'Error')

endtry

Log_Error must be in the procedure file which you set procedure to.

Derek Dowle wrote:

The changes that have been made to the code to date ensure that all works according to plan when working in the development environment.

The new problems only manifest themselves after the application has been built into and run from an executable file.

The first problems is; if the user changes their mind and closes the SaveAs dialog box by selecting Cancel, then this action not only closes the dialog box but the application as well and a Wait Window with DO CANCEL appears momentarily. How can I prevent the application from closing At this stage an error would not have occurred as no attempt had been made to save the Excel file.

Derek,

The problem is, I don't understand what is the dialog you're talking about here. Where is this dialog coming from Which particular line of code causes this dialog

Also what Excel version you're using

Derek Dowle wrote:

The second problem is, if the user attempts to copy over an existing open file the error is trapped and a message telling the user what has happened and what to do appears. The application returns to the form. It is only when the user selects another control that the code scrolls down the screen reminiscent of when ignore is pressed when a system error message appears on the screen. This behaviour must be caused by the initial error that was trapped in the TRY block. How can this problem be overcome

Lastly is there a general reason why code functions correctly in the development environment and not in runtime

Usually the difference may be caused by pathing, e.g. different paths are in IDE and in run-time, so some files may be not visible. Also are you sure you're running correct version of VFP (and SP)

We had a problem in my prev. work, when we had launcher application in VFP8 and the main application in VFP9. Took us some time to figure that since it was launched by extra little application, it was working in VFP8 as well.

Also, for instance, yesterday I was trying to figure out why application worked fine in IDE, but gave an error upon closing (file doesn't exist). It took me quite a long time to find out the offending line of code

SET HELP TO.

So, it may take quite a time to figure the problem out Sad





Re: ON ERROR causes controls on forms to disappear

Derek Dowle

Hi Naomi

Thank you for your understanding to date.

The version of Excel I am using is 2003 with SP2.

The version of vfp I am using is 9 with SP2. As I mentioned previously the application started its life in vfp3, was migrated to vfp6 some years ago and then migrated to vfp9 a month ago. The users have been using the application in runtime and I have not been advised of any problems since the most recent migration.

The dialog box that I refer to is generated by the code 'COPY TO (filename) XL5'. The dialog box is the dialog that appears when 'Save As' is selected from the 'File' menu in Microsoft Office application Word, Excel, VFP etc. By using the COPY TO code the Save As dialog box immediately appears showing the filename in the filename field and showing the appropriate directory as determined by the content of the variable filename.

Only when in runtime the user decides to abort the export process and presses the 'cancel' commandbutton on the dialog box the dialog box closes, a wait window displaying DO CANCEL appears and then the application closes down, just as if QUIT was included in the code. After the cancel commandbutton is selected I would expect the application to return to the calling form.

The instruction cancelling the dialog box seems to be passed onto the VFP runtime application. The problem does not occur when the application is run in IDE.

The second problem again only occurs in runtime and happens when attempting to overwrite an open file is blocked in the TRY\CATCH block. The way the application behaves is like when a vfp error message appears and the user selects 'ignore' and then the code scrolls down the screen. But in this case a VFP error message has not been displayed on the screen. Is there a clash somewhere between TRY\CATCH and ON ERROR Can ERROR be switched off during the TRY\CATCH block, and switch back on afterwards

This last problem is the most frustrating as the likelihood of the error actually happenning is minimal and when it does the application continues, albeit not in a clever fashion. It would be nice to it right for if and when the error is trapped.

If you cannot think of solutions to these problems please let me know and perhaps I will start another thread more particular to the task, as we have strayed somewhat from the initial question asked. Thank you for your efforts.





Re: ON ERROR causes controls on forms to disappear

Naomi Nosonovsky

Derek,

Something is wrong here, but it's hard to guess, so perhaps a new thread would be nice.

Anyway, I've just tried a simple test:

Code Snippet

ON ERROR MESSAGEBOX('On Error ' + MESSAGE())
USE sometable

LOCAL lcFileName
lcFileName = 'test.xls'
TRY
COPY TO (m.lcFileName) TYPE xl5
CATCH TO loException
=MESSAGEBOX('From Catch statement ' + loException.Message)
ENDTRY
USE
ON error

I then opened the xls and on the second attempt to create it got OLE error code 0x80030021: A lock violation has occurred from the catch statement. So, ON ERROR has no bearance if try/catch is in effect.

BTW, SP2 for VFP9 has not been released yet. You're probably using SP1. If you installed SP2, you should not been using it in production.





Re: ON ERROR causes controls on forms to disappear

Derek Dowle

Hi Naomi

Until your last response I was not aware that SP2 was in BETA! I am not a software professional, but someone who has been tasked since vfp3 with developing database applications for use in the workplace and my company's IT department policy is that they install all software so I get what I am given!

To revert to SP1 I assume that vfp9 will have to be reinstalled. As I am away from base for a few weeks I will have to wait.

As you appear to be experiencing the same problem with the Save As dialog on your sampler it cannot be the fact that I have SP2 BETA loaded that is causing my problems.

Once again thank you very much for your help.,





Re: ON ERROR causes controls on forms to disappear

Naomi Nosonovsky

Derek,

You somehow misunderstood me. I didn't see the Save As dialog you mentioned. I'm not sure where this dialog is coming up for you. I do see a messagebox with an error if I have the file opened, which is expected. And in my tests I confirmed, that this error is trapped by try/catch and not by the on error.

As for using beta SP2 in production - I don't know what to say and what effect does it cause on your applications. Until the final release it's better to use SP1, so you would have to uninstall SP2 at some point. If you will be doing this, AFAIK, there were threads here asking how to do that (and I certainly saw these threads on UT), because this process may not run smoothly.