error handling help

[Tailgun]

Here is the code it works just fine for copying and moving stuff into the database folder that contains all the images etc which are referenced in a SQL table. The error occurs when the user wants to MOVE a file to the database folder and that file already exsists in the folder. I Get an error '58' file already exsists. What I want to do is have a msg box stating that ( I have no problem with doing the msgbox)and the user click the ok in the msg box and be returned to the form so he can then move on to correcting the situation without exiting the app.
Any help would be appreciated
Thanks

Private Sub Command1_Click()
If Check4.Value = False And Check5.Value = False And Check6.Value = False Then
iReturnValue = MsgBox("C or S1 or S2 must be selected", vbOKOnly)
Else
Dim strFilename
Dim strPath
Dim i
Dim strSourceFileName
Dim strDestinationFileName

For i = 1 To frmImages.File1.ListCount
strFilename = (frmImages.File1.FileName)
strPath = (frmImages.File1.Path)
Next

Set fs = CreateObject("Scripting.FileSystemObject&quot
If chkKeep = 1 Then
fs.CopyFile Text1.Text, "c:\rp\"
ElseIf chkKeep = 0 Then
fs.MoveFile Text1.Text, "c:\rp\"
File1.Refresh
Set fs = Nothing
End If
Dim objConn
Set objConn = New ADODB.Connection
objConn.ConnectionString = "DSN=eDoan;UID=sa"
objConn.Open

Dim objRS
Set objRS = objConn.Execute("Select * from tblImages&quot

Dim strSQL

If objRS.RecordCount <> 0 Then

ClaimID = (frmImages.txtClaimNumber.Text)

End If
strPath2 = (&quot;C:\rp\&quot
strSQL = &quot;insert into tblImages (ClaimID, Description, Checked, CheckedS1, CheckedS2) values ('&quot; & frmImages.txtClaimNumber.Text & &quot;','&quot; & strPath2 & strFilename & &quot;','&quot; & Check4.Value & &quot;','&quot; & Check5.Value & &quot;','&quot; & Check6.Value & &quot;')&quot;

objConn.Execute (strSQL)

Set objRS = Nothing
objConn.Close
Set objConn = Nothing

iReturnValue = MsgBox(&quot;Image has been saved&quot;, vbOKOnly)
End If
End Sub

========
This is the area of concern
----------

Set fs = CreateObject(&quot;Scripting.FileSystemObject&quot
If chkKeep = 1 Then
fs.CopyFile Text1.Text, &quot;c:\rp\&quot;
ElseIf chkKeep = 0 Then
fs.MoveFile Text1.Text, &quot;c:\rp\&quot;
File1.Refresh
Set fs = Nothing
End If

 

[AndyDuke]

1) Add an 'On Error Goto ErrorHandler' statement at the start of the sub. If an error occurs the code will then jump to your error handler.

2) Add the code to handle the error at the bottom of the sub

ErrorHandler:
if (err.number = 58) then
msgbox &quot;The file already exists&quot;,vbInformation,&quot;Error&quot;
else
msgbox &quot;Unexpected error&quot;,vbcritical,&quot;error&quot;
end if
end sub

3) remember to add an exit sub statement before the error handler code otherwise you will fall into the error handler after a successful execution.

You can make the code in the errorhandler a bit more sophisticated, this is just an example to show you the idea.

Andy

 

[CajunCenturion]

You can use the Error Handler to trap the file exists error, but I am not a fan of using the error handler as part of the normal operating procedure. I think that a better approach is check for the existance of the file prior to attempting the move

Set fs = CreateObject(&quot;Scripting.FileSystemObject&quot
If chkKeep = 1 Then
fs.CopyFile Text1.Text, &quot;c:\rp\&quot;
ElseIf chkKeep = 0 Then
If fs.FileExists(Text1.Text) then
MsgBox &quot;The File Already Exists&quot;
<Do what is appropriate here>
Else
fs.MoveFile Text1.Text, &quot;c:\rp\&quot;
End If

File1.Refresh
Set fs = Nothing
End If

That being said, it is still a good idea to have the error handler in place to trap other errors that may occur during the flow.
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Tailgun]

Thanks to you both

It works just fine using Andy's code but I think I will also take Cajun's suggestion in to account because I think checking for the file being present first is maybe a better way to go

Thanks again

 

[CajunCenturion]

There is a mistake in my code Tailgun, it should read as follows:

If (fs.FileExits(Text1.Text)) Then
If fs.FileExists(&quot;c:\rp\&quot; & fs.GetFileName(Text1.Text)) Then
MsgBox &quot;Destination File Already Exists&quot;
Else
fs.MoveFile Text1.Text, &quot;c:\rp\&quot;
End If
Else
MsgBox &quot;Source File Does Not Exists&quot;
End If Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Tailgun]

Thanks for the correction Cajun