1

I am getting a weird problem in Access 2007 SP3. When I export a report to pdf I get the "Output To" prompt which I don't want, is there anything in my code I am doing wrong?

OverViewFile = DLookup("ExportPath", "dbo_Defaults") & "PC" & Format(Now(), "ddmmyy") & Format(Now(), "hhmm") & ".pdf"

DoCmd.OutputTo acOutputReport, "Rpt_ExportBPC", acFormatPDF, OverViewFile, False

If I've missed anything please let me know.

HansUp
  • 95,961
  • 11
  • 77
  • 135
user2294977
  • 111
  • 1
  • 3
  • 10

1 Answers1

1

I don't get that prompt from Access 2007 SP3 when adapting your OutputTo with the name of my report object and giving it a valid file path for OverViewFile. So I suspect your problem is due to OverViewFile; inspect the value of that string:

OverViewFile = DLookup("ExportPath", "dbo_Defaults") & "PC" & Format(Now(), "ddmmyy") & Format(Now(), "hhmm") & ".pdf"
Debug.Print OverViewFile
DoCmd.OutputTo acOutputReport, "Rpt_ExportBPC", acFormatPDF, OverViewFile, False

You can view the output from Debug.Print in the Immediate window (Ctrl+g will take you there).

Perhaps DLookup is returning Null. You would then have a valid VBA string for OverViewFile, but it would not be a valid Windows path.

There is another issue with OverViewFile which probably doesn't contribute to the problem, but I'll suggest this because it's simpler and I think you actually want hhnn instead of hhmm in the file name (n represents minute; m represents month)

OverViewFile = DLookup("ExportPath", "dbo_Defaults") & "PC" & _
    Format(Now(), "ddmmyyhhnn") & ".pdf"
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • Just curious: Does `OutputTo` prompt if the file already exists? If so, then a mm vs. nn mixup might cause that. – Gord Thompson Oct 29 '13 at 15:16
  • It didn't prompt for me when the destination file existed. Instead it silently replaced the old file. – HansUp Oct 29 '13 at 15:28
  • Thanks for your help HansUp, the dlookup definitely is not null. Just found that it works from .mdb but not a .mde? I've also changed the hhmm to hhnn. – user2294977 Oct 29 '13 at 17:14
  • Just a though, do I need to enable any VBA references to get this to work? – user2294977 Oct 29 '13 at 17:27
  • `DoCmd` is a method of the application object. So, if this were a references issue, I think you would have many more problems ... not limited to only `OutputTo`. – HansUp Oct 29 '13 at 18:39