Common Dialog and Cancel Button

I am using the Common Dialog control to allow a user to save an access report to a word doc(acutally RTF).  If the user press cancel on the save as form of the common Dialog i want it to exit the sub all together.

So far no such luck in it working.  It either stills saves the document or drops to the default save as for RTF files.

TIA
John
JTD3Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
blakeh1Connect With a Mentor Commented:
Here is a routine thaqt will check for the cancel error
Private Sub Command1_Click()
             Dim oCmnDlg As CommonDialog
             Dim strFormat As String
             Dim strFileName As String
             Set oCmnDlg = Me!cdlgSave.Object
             On Error GoTo Export_Err
             With oCmnDlg
                ' Clear the file name before prompting.
                .FileName = ""
                ' Prompt the user if the file already exists.
                .Flags = cdlOFNOverwritePrompt + cdlOFNHideReadOnly
                ' Determine if the cancel button was pressed.
                .CancelError = True
 
                .Filter = _
                "MS-DOS Text (*.txt)|*.txt|Rich Text Format (*.rtf)|*.rtf" _
                & "|Microsoft Excel (*.xls)|*.xls"
 
                .FilterIndex = 1
 
                ' Default the initial directory to "C:\My Documents".
                .InitDir = "C:\My Documents"
                .ShowSave
 
                ' Determine what file type was selected; then save the
                ' file in that format.
                Select Case .FilterIndex
                   Case 1 ' MS-DOS Text Format.
                      strFormat = acFormatTXT
 
                   Case 2 ' Rich Text Format.
                      strFormat = acFormatRTF
 
                   Case 3 ' Microsoft Excel Format.
                      strFormat = acFormatXLS
                End Select
                strFileName = .FileName
             End With
             DoCmd.OutputTo acOutputTable, "Generaltbl", _
                strFormat, strFileName
             Exit Sub
 
Export_Exit:
             Exit Sub
 
Export_Err:
             ' Check to see if the cancel button was pressed.
             If Err.Number = 32755 Then 'cancel was used
                Resume Export_Exit
             Else
                MsgBox Err.Number & ",    " & Err.Description
             End If
             Resume Export_Exit
End Sub
0
 
SeanGraflundCommented:
JTD3,

try this in your sub ..


Sub SaveAsRTF()

On Error Goto Err_Handle


'This is where your code is

Err_Handle:
    Exit Sub






0
 
JTD3Author Commented:
I have encluded the on error routine, but it does not trigger the exit of the function.

Any other ideas?

John
0
 
brewdogCommented:
Here is code that we use to do just what you're talking about:

Private Function FileDialog() as String

On Error GoTo FileDialogError

FileDialog = ""                   'start as empty string so we always have a string

With CommonDialog1
    .DialogTitle = sTitle
    .Filter = sFilter
    .Flags = lFlags
    .FileName = sFileName
    .ShowSave
End With

FileDialog = CommonDialog1.FileName

Exit Function

FileDialogError:
    MsgBox Err.Number & ", " & Err.Description
    FileDialog = ""

End Function

This way if they cancel and/or hit some kind of an error, this function still returns a string. You can do the same thing with the code by declaring a string variable, setting it to an empty string, and then setting it to an empty-string on the error, too.

Hope that helps . . .

brewdog
0
 
JTD3Author Commented:
Worked like a charm.  Thank you very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.