Help with Input Box (1 Viewer)

[ascaife]

Hi Guys,

Just a quick one.

I use the following code to export my query to file but I've found a bit of a bug in my code.

When the user clicks cancel on the input box, my next messagebox still comes up and then errors out. Could somebody please advise on how to deal with this by cancelling the rest of the event if cancel button is clicked on input box. As you can see, I'm no VBA expert so if you could outline in full it would be greatly appreciated.

Private Sub Command4_Click()
DoCmd.TransferText acExportFixed, "VDU", "qryVDU", "C:\FHM\VDU.txt", No
DoCmd.CancelEvent
Dim strFilename As String
strFilename = "C:\FHM\" & InputBox("Enter VDU File Name:", "FHM")
Dim vResponse As VbMsgBoxResult
If Dir(strFilename) = "" Then
Name "C:\FHM\VDU.txt" As strFilename
Else
vResponse = MsgBox("That file already exists. Would you like to overwrite it?", vbQuestion + vbYesNo, "Overwrite File?")
If vResponse = vbYes Then
Kill strFilename
Name "C:\FHM\VDU.txt" As strFilename
End If
End If
End Sub

 

[khawar]

Try this

Private Sub Command4_Click()

DoCmd.TransferText acExportFixed, "VDU", "qryVDU", "C:\FHM\VDU.txt", No
DoCmd.CancelEvent

Dim strFilename As String

strFilename = InputBox("Enter VDU File Name:", "FHM")

If len(strfilename)>0 then
     strFilename = "C:\FHM\" & strFilename

     Dim vResponse As VbMsgBoxResult

     If Dir(strFilename) = "" Then
          Name "C:\FHM\VDU.txt" As strFilename
     Else
          vResponse = MsgBox("That file already exists. Would you like to overwrite it?", vbQuestion + vbYesNo, "Overwrite File?")

          If vResponse = vbYes Then
               Kill strFilename 
               Name "C:\FHM\VDU.txt" As strFilename
          End If

     End If

End If
End Sub

 

[ascaife]

Genius....

That worked a treat, thanks so much for the assistance it is very much appreciated.