By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,949 Members | 2,043 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,949 IT Pros & Developers. It's quick & easy.

Access 2007 hanging on exit, after exporting report to PDF

patjones
Expert 100+
P: 931
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdExportReport_Click()
  2.  
  3. Dim strFolder As String
  4. Dim fd As FileDialog
  5.  
  6. Set fd = Application.FileDialog(msoFileDialogFolderPicker)
  7.  
  8. If fd.Show = -1 Then
  9.     strFolder = fd.SelectedItems.Item(1)
  10. Else
  11.     Exit Sub
  12. End If
  13.  
  14. DoCmd.OutputTo acOutputReport, "rptEmployeeBirthDates", acFormatPDF, strFolder & "\" & Report_rptEmployeeBirthDates.Caption & ".pdf"
  15.  
  16. Set fd = Nothing
  17.  
  18. 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
May 1 '10 #1

✓ answered by 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

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "rptEmployeeBirthDates", acViewDesign, "", "", acHidden
  2. mycaption = Reports!rptEmployeeBirthDates.Caption
  3. DoCmd.Close , "rptEmployeeBirthDates", acSaveNo
  4. DoCmd.OutputTo acOutputReport, "rptEmployeeBirthDates", acFormatPDF, strFolder & "\" & mycaption & ".pdf", False, "", 0, acExportQualityPrint
Other than this I never had any hangs or misfits

Share this Question
Share on Google+
11 Replies


patjones
Expert 100+
P: 931
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
May 2 '10 #2

Jim Doherty
Expert 100+
P: 897
@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?
May 2 '10 #3

NeoPa
Expert Mod 15k+
P: 31,492
I would hazard Access 2007 Jim ;)
May 2 '10 #4

P: 37
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
May 2 '10 #5

NeoPa
Expert Mod 15k+
P: 31,492
As a matter of logic, I would consider re-ordering the code to reflect the basics of what's happening :
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdExportReport_Click()
  2.  
  3.     Dim strFolder As String
  4.     Dim fd As FileDialog
  5.  
  6.     Set fd = Application.FileDialog(msoFileDialogFolderPicker)
  7.  
  8.     If fd.Show Then
  9.         strFolder = fd.SelectedItems.Item(1)
  10.         DoCmd.OutputTo acOutputReport, _
  11.                        "rptEmployeeBirthDates", _
  12.                        acFormatPDF, _
  13.                        strFolder & "\" & Report_rptEmployeeBirthDates.Caption & ".pdf"
  14.     End If
  15.  
  16.     Set fd = Nothing
  17.  
  18. 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)?
May 2 '10 #6

Jim Doherty
Expert 100+
P: 897
@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

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "rptEmployeeBirthDates", acViewDesign, "", "", acHidden
  2. mycaption = Reports!rptEmployeeBirthDates.Caption
  3. DoCmd.Close , "rptEmployeeBirthDates", acSaveNo
  4. DoCmd.OutputTo acOutputReport, "rptEmployeeBirthDates", acFormatPDF, strFolder & "\" & mycaption & ".pdf", False, "", 0, acExportQualityPrint
Other than this I never had any hangs or misfits
May 2 '10 #7

patjones
Expert 100+
P: 931
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
May 2 '10 #8

P: 37
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
May 2 '10 #9

patjones
Expert 100+
P: 931
Jim, that works great. Incorporating NeoPa's suggestion, the code is as follows:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdExportReport_Click()
  2.  
  3. Dim strFolder As String
  4. Dim strCaption As String
  5. Dim fd As FileDialog
  6.  
  7. Set fd = Application.FileDialog(msoFileDialogFolderPicker)
  8.  
  9. If fd.Show Then
  10.  
  11.     strFolder = fd.SelectedItems.Item(1)
  12.  
  13.     DoCmd.OpenReport "rptEmployeeBirthDates", acViewDesign, , , acHidden
  14.     strCaption = Reports!rptEmployeeBirthDates.Caption
  15.     DoCmd.Close acReport, "rptEmployeeBirthDates", acSaveNo
  16.  
  17.     DoCmd.OutputTo acOutputReport, "rptEmployeeBirthDates", acFormatPDF, strFolder & "\" & strCaption & ".pdf"
  18.  
  19. End If
  20.  
  21. Set fd = Nothing
  22.  
  23. End Sub

Thanks!

Pat
May 4 '10 #10

Jim Doherty
Expert 100+
P: 897
@zepphead80
You,re very welcome Pat :)
May 4 '10 #11

NeoPa
Expert Mod 15k+
P: 31,492
8-) Pat. I'm very pleased to have been a little help at least.
May 4 '10 #12

Post your reply

Sign in to post your reply or Sign up for a free account.