Access 2007 hanging on exit, after exporting report to PDF

[patjones]

Hi,

It feels strange to be the one doing the asking after so much answering, but this one is driving me crazy.

I have a simple database with a table, a form, and a report based on the table. It's something I made quickly to test out an idea, and so the table only has four or five records of useless data in it.

The idea is for the user to click a command button on the form, which will cause a system folder picker to display. Upon selecting a location and hitting OK, the report in the database gets exported to that location as a PDF named according to the report's caption. The code in the On Click event for the button is as follows:

Private Sub cmdExportReport_Click()
 
Dim strFolder As String
Dim fd As FileDialog
 
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
 
If fd.Show = -1 Then
    strFolder = fd.SelectedItems.Item(1)
Else
    Exit Sub
End If
 
DoCmd.OutputTo acOutputReport, "rptEmployeeBirthDates", acFormatPDF, strFolder & "\" & Report_rptEmployeeBirthDates.Caption & ".pdf"
 
Set fd = Nothing
 
End Sub

This works fine, but when I subsequently try to close the database, Access freezes and I have to shut it down using Task Manager. Now, if I open the folder picker and hit "Cancel" (no exporting going on), I have no problem. It's only if Access actually exports the report that I run into trouble.

Any ideas?

Pat

[patjones]

I should add that when I manually do the export by right-clicking on the report in print preview and selecting the PDF option, I don't have any problem exiting Access. So the title of my thread might be a little misleading as it probably has less to do with exporting something to a PDF and more to do with the system folder picker box. Thanks.

Pat

[Jim Doherty]

@zepphead80
subscribing - I,ll see what I can do on my laptop to replicate your position which version access you using I have 2000 and 2007 only on my machine?

[NeoPa]

I would hazard Access 2007 Jim ;)

[robjens]

I have the same with 2003 on this machine when I close the application. Everything exits fine but the MDI freezes. Run vista on a quad here and never saw that anywhere else ... weird stuff

[NeoPa]

As a matter of logic, I would consider re-ordering the code to reflect the basics of what's happening :

Private Sub cmdExportReport_Click()
 
    Dim strFolder As String
    Dim fd As FileDialog
 
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
 
    If fd.Show Then
        strFolder = fd.SelectedItems.Item(1)
        DoCmd.OutputTo acOutputReport, _
                       "rptEmployeeBirthDates", _
                       acFormatPDF, _
                       strFolder & "\" & Report_rptEmployeeBirthDates.Caption & ".pdf"
    End If
 
    Set fd = Nothing
 
End Sub

Can you let us know if this gives the same problem (I see nothing specific that could be thought of as wrong in your current code by the way)?

[Jim Doherty]

@NeoPa
Silly me! PDF format......... not Access 2000 even sillier the title says Access 2007 haha a senior moment I think

OK If this is Access 2007 (which I hasten to add I am not savvy with yet) then it certainly did not like the command line to output to PDF using the caption property of a report where we are concatenating the folder and caption property as filename destination for the file. To grab this I had to open the report hidden in design grab it to a variable and then do the rest

I used the code for the filepicker posted only the following portion differs

DoCmd.OpenReport "rptEmployeeBirthDates", acViewDesign, "", "", acHidden
mycaption = Reports!rptEmployeeBirthDates.Caption
DoCmd.Close , "rptEmployeeBirthDates", acSaveNo
DoCmd.OutputTo acOutputReport, "rptEmployeeBirthDates", acFormatPDF, strFolder & "\" & mycaption & ".pdf", False, "", 0, acExportQualityPrint

Other than this I never had any hangs or misfits

[patjones]

Ohhh, but I think you might be on to something here. I am not opening the report first; I have a feeling that's going to fix it. I'll try it out later when I have time and let you know. Thanks for looking into it!

Pat

[robjens]

I had a situation with .pdf too and no real solution since there wasn't any pdf output format in Access. We did have distiller (and thus the pdf printer) so I took this approach

• Set Application.Printer = Application.Printers("Adobe PDF")

The drawback is that it's hard to automate the user response to the dialog from Acrobat and I went through some length to find short usuable code for sending keystrokes to the dialog but never really got it. Must have spent at least 2 days looking. So I had to disable the dialog box and set the output directory for the file manually on some workstations. Which is ok on a smaller scale I guess.

But since there is a vb const now for .pdf, not having worked with it myself, I guess would favour depending how long you wanna spend getting it to work ;)

Also I never got passed that problem where a +1 row counter made during print of the Access form, would after printing to .pdf keep counting so my acc report would say 1 to 50, and then 50 to 100 on the pdf file... Ugh

[patjones]

Jim, that works great. Incorporating NeoPa's suggestion, the code is as follows:

Private Sub cmdExportReport_Click()
 
Dim strFolder As String
Dim strCaption As String
Dim fd As FileDialog
 
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
 
If fd.Show Then
 
    strFolder = fd.SelectedItems.Item(1)
 
    DoCmd.OpenReport "rptEmployeeBirthDates", acViewDesign, , , acHidden
    strCaption = Reports!rptEmployeeBirthDates.Caption
    DoCmd.Close acReport, "rptEmployeeBirthDates", acSaveNo
 
    DoCmd.OutputTo acOutputReport, "rptEmployeeBirthDates", acFormatPDF, strFolder & "\" & strCaption & ".pdf"
 
End If
 
Set fd = Nothing
 
End Sub

Thanks!

Pat

[Jim Doherty]

@zepphead80
You,re very welcome Pat :)

[NeoPa]

8-) Pat. I'm very pleased to have been a little help at least.